PostgreSQL: a valid variable assignation sample?

Question

After reading this question, I'm trying to convert some SQL from MySQL to PostgreSQL. Thus I need variable assignation:

INSERT INTO main_categorie (description) VALUES ('Verbe normal');
SET @PRONOMINAL := SELECT LAST_INSERT_ID();

INSERT INTO main_mot (txt,im,date_c,date_v_d,date_l)
VALUES ('je m''abaisse',1,NOW(),NOW(),NOW());
SET @verbe_149 = SELECT LAST_INSERT_ID();

INSERT INTO main_motcategorie (mot_id,categorie_id) VALUES (@verbe_149,@PRONOMINAL);

How would you do this with PostgreSQL? No useful sample in the documentation of v9 and v8 (almost the same). NB: I dont want to use a stored procedure like here, I just want "raw sql" so I can inject it through CLI interface.

score 2 · Accepted Answer · edited May 23 '17 at 12:31

There are no variables in Postgres SQL (you can use variables only in procedural languages).

Use RETURNING in WITH query:

WITH insert_cat AS (
    INSERT INTO main_categorie (description)
    VALUES ('Verbe normal')
    RETURNING id
),
insert_mot AS (
    INSERT INTO main_mot (txt,im,date_c,date_v_d,date_l)
    VALUES ('je m''abaisse',1,NOW(),NOW(),NOW())
    RETURNING id
)
INSERT INTO main_motcategorie (mot_id,categorie_id) 
SELECT m.id, c.id
FROM insert_mot m, insert_cat c;

As an alternative, you can use custom configuration parameters in the way described in this post.

Create two functions:

create or replace function set_var (name text, value text)
returns void language plpgsql as $$
begin
    execute format('set mysql.%s to %s', name, value);
end $$;

create or replace function get_var (name text)
returns text language plpgsql as $$
declare
    rslt text;
begin
    execute format('select current_setting(''mysql.%s'')', name) into rslt;
    return rslt;
end $$;

With the functions you can simulate variables, like in the example:

INSERT INTO main_categorie (description)
VALUES ('Verbe normal');

SELECT set_var('PRONOMINAL', (SELECT currval('main_categorie_id_seq')::text));

INSERT INTO main_mot (txt,im,date_c,date_v_d,date_l)
VALUES ('je m''abaisse',1,NOW(),NOW(),NOW());

SELECT set_var('verbe_149', (SELECT currval('main_mot_id_seq')::text));

INSERT INTO main_motcategorie (mot_id,categorie_id) 
SELECT get_var('verbe_149')::int, get_var('PRONOMINAL')::int;

This is certainly not an example of good code. Particularly the necessity of casting is troublesome. However, the conversion can be done semi-automatically.

OMG! I'm generating a script for MySQL: it creates tons of variables (=automation) with `INSERT INTO` and note the result in variables with things like `SET myvar_5460=LAST_INSERT_ID()`. And later on, say 6000000 lines later, I might need this `myvar_5460` to insert it into another relation like `INSERT INTO ... VALUES(@myvar_5460)`. So it seems impossible to do that with PostgreSQL? — Olivier Pons, Dec 27 '15 at 22:36
You can define them in `postgresql.conf` to have default values in every database; or use `ALTER DATABASE ... SET ...` to permanently change values for a database. See http://stackoverflow.com/a/34476203/1995738 — klin, Dec 28 '15 at 00:12
I meant "I hope they are only temporary even though this is not a good thing to do stuff like that" and that when I close my client, they will be gone forever! — Olivier Pons, Dec 28 '15 at 00:13
Yes, when used like in the answer they are local in the session and temporary. — klin, Dec 28 '15 at 00:14

Andomar · Answer 2 · 2015-12-27T22:31:02.097

You can run PostgreSQL scripts outside of a function using the do construct. Here's an example with Donald Ducks' nephews. First the nephew will be added to the nephew table, and then we'll add a baseball cap using the newly inserted nephew's id.

First, create two tables for nephews and baseball caps:

drop table if exists nephew;
drop table if exists cap;
create table nephew (id serial primary key, name text);
create table cap (id serial, nephewid bigint, color text);

Now add the first nephew:

do $$declare
  newid bigint;
begin
  insert into nephew (name) values ('Huey') returning id into newid; 
  insert into cap (nephewid, color) values (newid, 'Red');
end$$;

The returning ... into ... does in Postgres what currval does in MySQL. Huey's new id is assigned to the newid variable, and then used to insert a new row into the cap table. You can run this script just like any other SQL statement. Continue with Dewey and Louie:

do $$declare
  newid bigint;
begin
  insert into nephew (name) values ('Dewey') returning id into newid; 
  insert into nephew (name) values ('Louie') returning id into newid; 
  insert into cap (nephewid, color) values (newid, 'Green');
end$$;

And you end up with:

# select * from nephew;
 id | name  
----+-------
  1 | Huey
  2 | Dewey
  3 | Louie
(3 rows)

# select * from cap;
 id | nephewid | color 
----+----------+-------
  1 |        1 | Red
  2 |        3 | Green
(2 rows)

See it working at SQL Fiddle.

OMG! I'm generating a script for MySQL: it creates tons of variables (=automation) with `INSERT INTO` and note the result in variables with things like `SET myvar_5460=LAST_INSERT_ID()`. And later on, say 6000000 lines later, I might need this `myvar_5460` to insert it into another relation like `INSERT INTO ... VALUES(@myvar_5460)`. So it seems impossible to do that with PostgreSQL? — Olivier Pons, Dec 27 '15 at 22:36
Pretty sure Postgres' scripting environment is powerful enough for that. You could just use a 6M line `do` script. However, it is probably better to see if you can split the generated script into smaller autonomous parts. For example, you can refer to Hewey's id later on like `(select id from nephew where name = 'Hewey')`. There is often no need to keep variables around for very long. — Andomar, Dec 27 '15 at 22:39

PostgreSQL: a valid variable assignation sample?

2 Answers2

Linked