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 stackoverflow.com/questions/36959/… — beerbajay
– beerbajay, Commented Mar 16, 2015 at 8:24
I tried in the pgAdmin III sql editor but it says something like syntax error near "\" — Fagner Brack
– Fagner Brack, Commented Mar 16, 2015 at 8: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
– beerbajay, Commented Mar 16, 2015 at 8:36

user330315 · Accepted Answer · 2015-03-16 08:53:02Z

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

answered Mar 16, 2015 at 8:53

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Fagner Brack Over a year ago

Man... why isn't SQL just like a normal language?

user330315 Over a year ago

@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.

Neil McGuigan · Accepted Answer · 2015-03-16 20:47:14Z

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, 2015 at 20:47

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

Collectives™ on Stack Overflow

How can I store a variable in a postgresql script?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related