CODEWITHSUNDEEP
sqlpostgresqlselectvariable-assignment
Marcio Mazzucato
Marcio Mazzucato

Reputation: 9305

Is it possible to assign a select field to a variable in PostgreSQL?

I have to get a value from a select and create a variable to use in a insert, is it possible in PostgreSQL? I can't create a function because my hosting don't allow this.

Here is the table creation schema:

CREATE SEQUENCE identificador_produto START 1;

CREATE TABLE product (
    id integer DEFAULT nextval('identificador_produto') primary key,
    description varchar(60) not null
);

CREATE TABLE material (
    product_id integer DEFAULT nextval('identificador_produto') primary key,
    description varchar(60) not null
);

Example to ilustrate what i need:

begin;

    select currval('identificador_produto') as id;

    insert into material (product_id, description) values (id, 'Gold');
    insert into material (product_id, description) values (id, 'Silver');
    insert into material (product_id, description) values (id, 'Wood');
    insert into material (product_id, description) values (id, 'Steel');
    insert into material (product_id, description) values (id, 'Water');
    insert into material (product_id, description) values (id, 'Paper');

commit;

I have to assign the 'id' value to a variable and them use it to do some inserts in the same transaction, but the inserts share the same sequence of 'product', because of this i can't use the curval() function, otherwise each insert will get a different id.

Can anybody help me?

Upvotes: 1

Views: 8031

Answers (4)

user330315
user330315

Reputation:

First I think your understanding of currval is wrong. currval will return the last generated id that was taken from your sequence.

The following will insert all rows with the same id:

insert into material (product_id, description) 
values (nextval('identificador_produto'), 'Gold');

insert into material 
(product_id, description) 
values 
(currval('identificador_produto'), 'Silver');

insert into material 
(product_id, description) 
values 
(currval('identificador_produto'), 'Wood');

insert into material 
(product_id, description) 
values 
(currval('identificador_produto'), 'Steel');

insert into material 
(product_id, description) 
values 
(currval('identificador_produto'), 'Water');

insert into material 
(product_id, description) 
values 
(currval('identificador_produto'), 'Paper');

The first one generates a new ID and the subsequent INSERTs re-use that same id. This is transaction safe and will work correctly even when thousands of connections do this (that's the beauty of sequences)

But I think your table design is wrong. The way you defined the tables, you could just move the description column to the product table.

My guess is that you actually want something like this: 

CREATE SEQUENCE identificador_produto START 1;

CREATE TABLE product (
    id integer DEFAULT nextval('identificador_produto') primary key,
    description varchar(60) not null
);

CREATE TABLE material (
    material_id serial primary key,
    product_id integer not null 
       references product(id),
    description varchar(60) not null
);

Which means you have one or more rows in material that reference a specific product. The insert pattern would then look something like this:

insert into product
(description) 
values 
('foobar');

insert into material 
(product_id, description)
values
(currval('identificador_produto'), 'silver');

insert into material 
(product_id, description)
values
(currval('identificador_produto'), 'gold');

Upvotes: 4

kgrittn
kgrittn

Reputation: 19471

If you're looking to do both inserts with the same id value, in the same transaction, this might be a good alternative:

WITH x(descr) AS (VALUES ('Gold'), ('Silver'))
INSERT INTO material (product_id, description)
  SELECT id, descr
    FROM x,
        (SELECT id FROM product WHERE condition = 100) y;

The other possibility, if you really need imperative code with variables, is a DO construct:

http://www.postgresql.org/docs/current/interactive/sql-do.html

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

You don't need a variable for that:

insert into material (product_id, description) values (
    (select id from product where condition = 100)
    , 'Gold');

Upvotes: 2

Nikhil Baliga
Nikhil Baliga

Reputation: 1341

Yeah, first declare a variable in the declare section.

Then say "select id into variable from product where condition = 100;" and continue

    declare
    myvar int;
    begin
    select id into myvar from product where condition = 100;

    ...
    end;

Upvotes: 1

Related Questions