Iterate over ARRAY, declare temp table. postgresql

Question

I am passing a list of ids, I need to iterate over that list and make SELECTIONS.

CREATE TABLE IF NOT EXISTS unique_things_block 
(
    id SERIAL PRIMARY KEY,
    unique_thing_block_name VARCHAR(64)
);

CREATE TABLE IF NOT EXISTS unique_thing 
(
    id SERIAL PRIMARY KEY,
    unique_thing_name VARCHAR(64),
    unique_thing_block INTEGER references unique_things_block(id),
    unique_thing_description VARCHAR(256),
    is_boolean boolean NOT NULL
);

CREATE TABLE IF NOT EXISTS permutations 
(
    id SERIAL NOT NULL,
    unique_thing_id INTEGER references unique_thing(id),
    PRIMARY KEY(id, unique_thing_id)

);


INSERT INTO unique_thing 
VALUES(1, 'dgsg', 1, 'It means that blablabla...', FALSE);

INSERT INTO unique_thing 
VALUES(2, 'dfgdfg', 1, 'Too long text...', FALSE);

INSERT INTO unique_thing 
VALUES(3, 'sdf wdfs', 2, 'It means that blablabla...', FALSE);

INSERT INTO unique_thing 
VALUES(4, 'sdf fdg fdg', 2, 'Too long text...', FALSE);

INSERT INTO unique_thing 
VALUES(5, 'dfg dfg', 2, 'dfg Too long text...', FALSE);

INSERT INTO unique_thing 
VALUES(6, 'dfg dfg', 2, 'df Too long text...', FALSE);

INSERT INTO unique_thing 
VALUES(7, 'dfg', 3, 'How to make...', TRUE);

INSERT INTO unique_thing 
VALUES(8, 'dfg', 3, 'asdasdsd...', TRUE);

INSERT INTO permutations VALUES(1, 1);
INSERT INTO permutations VALUES(1, 3);
INSERT INTO permutations VALUES(1, 7);
INSERT INTO permutations VALUES(2, 1);
INSERT INTO permutations VALUES(2, 3);
INSERT INTO permutations VALUES(2, 8);
INSERT INTO permutations VALUES(3, 1);
INSERT INTO permutations VALUES(3, 4);
INSERT INTO permutations VALUES(3, 7);
INSERT INTO permutations VALUES(4, 1);
INSERT INTO permutations VALUES(4, 4);
INSERT INTO permutations VALUES(4, 8);
INSERT INTO permutations VALUES(5, 1);
INSERT INTO permutations VALUES(5, 5);
INSERT INTO permutations VALUES(5, 7);
INSERT INTO permutations VALUES(6, 1);
INSERT INTO permutations VALUES(6, 5);
INSERT INTO permutations VALUES(6, 8);
INSERT INTO permutations VALUES(7, 1);
INSERT INTO permutations VALUES(7, 6);
INSERT INTO permutations VALUES(7, 7);
INSERT INTO permutations VALUES(8, 1);
INSERT INTO permutations VALUES(8, 6);
INSERT INTO permutations VALUES(8, 8);
INSERT INTO permutations VALUES(9, 2);
INSERT INTO permutations VALUES(9, 3);
INSERT INTO permutations VALUES(9, 7);
INSERT INTO permutations VALUES(10, 2);
INSERT INTO permutations VALUES(10, 3);
INSERT INTO permutations VALUES(10, 8);
INSERT INTO permutations VALUES(11, 2);
INSERT INTO permutations VALUES(11, 4);
INSERT INTO permutations VALUES(11, 7);
INSERT INTO permutations VALUES(12, 2);
INSERT INTO permutations VALUES(12, 4);
INSERT INTO permutations VALUES(12, 8);
INSERT INTO permutations VALUES(13, 2);
INSERT INTO permutations VALUES(13, 5);
INSERT INTO permutations VALUES(13, 7);
INSERT INTO permutations VALUES(14, 2);
INSERT INTO permutations VALUES(14, 5);
INSERT INTO permutations VALUES(14, 8);
INSERT INTO permutations VALUES(15, 2);
INSERT INTO permutations VALUES(15, 6);
INSERT INTO permutations VALUES(15, 7);
INSERT INTO permutations VALUES(16, 2);
INSERT INTO permutations VALUES(16, 6);
INSERT INTO permutations VALUES(16, 8);

But could you please help to do it? In mysql we can just create a cursor (CREATE temporary table) and insert into it.

How but can I do it in posgresql? I will run it as

SELECT * FROM get_things_by_id(ARRAY[1,3]);

DROP function get_things_by_id(ids integer[]);

CREATE OR REPLACE FUNCTION get_things_by_id(ids integer[])
  RETURNS table(pr INTEGER, cons INTEGER) AS $$
BEGIN

CREATE TEMP TABLE found_items ON COMMIT DROP AS(
  id INTEGER
);

CREATE TEMP TABLE found_unique_things ON COMMIT DROP AS(
  id INTEGER
);

DECLARE
  temp_result found_items;

DECLARE
  temp_result_found_unique_things found_unique_things;

SELECT id INTO temp_result FROM ids;

for i in temp_result:
    SELECT id INTO temp_result_found_unique_things FROM permutations where  
    WHERE unique_thing_id = $0') using i;

for g in temp_result_found_unique_things:
    make another selections


END
$$ LANGUAGE plpgsql;

I've created this function, but it shows me the error:

ERROR:  syntax error at or near "problem_id"
LINE 6:   problem_id INTEGER,

I do not understand why it happens because there is no detailed info about the error.

the other strange - your example doesn't contains "problem_id". When I have — Pavel Stehule
– Pavel Stehule, Commented Dec 13, 2015 at 18:10

Pavel Stehule · Accepted Answer · 2015-12-13 05:01:48Z

1

The code is pretty messy.

The PostgreSQL plpgsql function have to have syntax described in documentation.
Cycle FOR i IN temp_result: looks like Python cycle, not like PLpgSQL cycle.
Creating temporary tables cannot be in DECLARE part of function body. These statements should in function body (after BEGIN keyword).
The arrays and tables in Postgres are different objects, you cannot to mix them.
Don't use compose variables for single field values - it is slower.
Clause USING should be used on in EXECUTE statement.

It is looking, so you want to iterate over array

CREATE OR REPLACE FUNCTION foo(ids int[])
RETURNS TABLE(...) AS $$
DECLARE
   i int;
   temp_result_id int;
BEGIN
  FOREACH i IN ARRAY ids
  LOOP
    SELECT id INTO temp_result_id FROM permutations
      WHERE unique_thing_id = i;
    ...
  END LOOP;
END;
$$ LANGUAGE plpgsql;

There is big rule - What can be done with SQL, should be done with SQL.. For this case, you don't need a iteration over array - just use =ANY operator and reduce number of nested cycles:

SELECT .. FROM tab WHERE unique_thing_id = ANY(ids)

answered Dec 13, 2015 at 5:01

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

1 Comment

user565447 Over a year ago

I think I have explained it incorrectly. ANY return a lot of row, it's wrong here. When I find ARRAY[1,3,7], the function must return only one row, because it happens for the only id 1. There is the only case in permutations table: row 1 3, row 1 1, row 1 7. The first value is id. So,1 3, 7 is for id 1 only

Collectives™ on Stack Overflow

Iterate over ARRAY, declare temp table. postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related