How can I store a variable in a postgresql script?

Question

I have the following script where I need to find a given chapter, change the state, then store the activity reference to remove the activity later (because of the FK in chapter_published activity), delete the chapter_published reference and then use the id_activity to finally remove the parent activity.

How would I do that programatically using a postgresql script in a very simple way? And where is that documented?

Below is an example of what I would be expecting to achieve:

-- Manually find the chapter I want first
select * from ws_chapter;
-- store the chapter once so I don't have to repeat in each statement
@chapter_id = 15;
-- Update the state field
update chapter set cd_state = 'DRAFT' where id_chapter = @chapter_id;
-- Now get the id of the activity for later use
@activity_id = select id_activity from chapter_published where id_chapter = @chapter_id;
-- Make the delete
delete from chapter_published where id_chapter = @chapter_id;
delete from activity where id_activity = @activity_id;

• beerbajay: You probably want \set, see stackoverflow.com/questions/36959/…
• Fagner Brack: I tried in the pgAdmin III sql editor but it says something like syntax error near "\"
• beerbajay: From the comments in the previous post, it seems that \set is for the psql tool and probably won't work in pgAdmin or in a stored procedure.

Answer 1

You don't really need a variable for this specific case. You can achieve this with a single data modifying common table expression:

with updated as (
 update chapter 
 set cd_state = 'DRAFT'
 where id_chapter = 15
 returning id_chapter
), delete_published as (
 delete from chapter_published
 where id_chapter in (select id_chapter from updated)
 returning id_activity
)
delete from activity 
where id_activity in (select id_activity from delete_published);

If you want to have some kind of "variable" definition you could do by using one CTE at the beginning:

with variables (chapter_id) as (
 values (15)
), updated as (
 update chapter 
 set cd_state = 'DRAFT'
 where id_chapter = (select chapter_id from variables)
), delete_published as (
 ...
)
...

• Fagner Brack: Man... why isn't SQL just like a normal language?
• user330315: @FagnerBrack: because it's a query language not a programming language. For most DBMS "variables" are handled by the SQL client. Or you can do that in a procedural (=programming) language where you do have variables.

Answer 2

You can do this using an inline PLPGSQL function:

do $$
declare
 chapter_id int = 15;
begin
 //do stuff;
end $$;

Or setup a GUC in postgresql.conf called my, then

set my.chapter_id = 15;
select current_setting('my.chapter_id'); --not typesafe