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;

You probably want `\set`, see http://stackoverflow.com/questions/36959/how-do-you-use-script-variables-in-postgresql — beerbajay, Mar 16 '15 at 08:24
I tried in the pgAdmin III sql editor but it says something like `syntax error near "\"` — Fagner Brack, Mar 16 '15 at 08:35
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. — beerbajay, Mar 16 '15 at 08:36

score 1 · Accepted Answer · answered Mar 16 '15 at 08:53

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 (
 ...
)
...

@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. — , Mar 16 '15 at 11:06

score 0 · Answer 2 · answered Mar 16 '15 at 20:47

0

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

answered Mar 16 '15 at 20:47

Neil McGuigan

46,580
12
123
152

How can I store a variable in a postgresql script?

2 Answers2