Loop over query result in postgresql without using a predefined table

Question

I've created a function to loop over query results and it works fine. I'd like to have it work without my having to pre-create a table to store the results. Ie, I just want to iterate over the rows. Thing is, I'm not sure how to declare a record that is the same type as the rows returned when there is nothing to check against.

Here is my current function:

CREATE OR REPLACE FUNCTION dimensions.testing()
RETURNS void
LANGUAGE plpgsql
AS $body$
DECLARE rec myschema.tmpfiles%rowtype;
BEGIN

insert into myschema.tmpfiles(file_name, log_date)  
    SELECT f.file_name, f.log_date from dblink('conn', 'select 
file_name, log_date from myschema.process_tracker where 
isprocessed = FALSE') as f(file_name varchar, log_date date);

IF EXISTS (SELECT 1 FROM myschema.tmpfiles) THEN
    for rec in select * from myschema.tmpfiles
loop
        RAISE NOTICE '%', rec.file_name;
        RAISE NOTICE '%', rec.log_date;
        RAISE NOTICE '---------------------------';
end loop;           
ELSE
    --DO SOMETHING
END IF;

END;
$body$

Works fine, but it needs a predefined table in order for me to do this: (DECLARE rec myschema.tmpfiles%rowtype;) and get the row type.

How to loop over this query without pre-defining the result table?

Thanks!

declare rec record?

user330315
– user330315

2018-04-20 18:51:17 +00:00
Commented Apr 20, 2018 at 18:51 — user330315
– user330315, Commented Apr 20, 2018 at 18:51

BShaps · Accepted Answer · 2018-04-20 18:50:47Z

1

The RECORD type is a row-type variable with no predefined structure.

You declare it by doing name RECORD;

Record variables are similar to row-type variables, but they have no predefined structure. They take on the actual row structure of the row they are assigned during a SELECT or FOR command. The substructure of a record variable can change each time it is assigned to. A consequence of this is that until a record variable is first assigned to, it has no substructure, and any attempt to access a field in it will draw a run-time error.

Change myschema.tmpfiles%rowtype to RECORD and you should be fine.

answered Apr 20, 2018 at 18:50

BShaps

1,4248 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Loop over query result in postgresql without using a predefined table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related