3

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.

Improve this question

2 Answers 2

Reset to default
2

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.

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

5 Comments

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?
Are those custom configuration parameter only temporary?
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 stackoverflow.com/a/34476203/1995738
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!
Yes, when used like in the answer they are local in the session and temporary.
1

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.

Huey, Dewey and Louie from Wikipedia

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.

Improve this answer

2 Comments

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.