How to return a table from iteration in function with postgres 11

Question

Following functions are created for doing housekeeping within the database (PostgreSQL 11.4).

entity_with_multiple_taskexec: returns a list of entities for which the housekeeping should be done.
row_id_to_delete: returns tuples of id's to delete

Just for completeness, the function which works fine:

CREATE OR REPLACE  FUNCTION entity_with_multiple_taskexec() 
    RETURNS TABLE(entitykey varchar) AS 
$func$
BEGIN
RETURN QUERY select distinct task.entitykey from
    (select  task.entitykey from task where dtype = 'PropagationTask' group by task.entitykey having count(*) > (select count(*) from conninstance)) more_than_one_entry
inner join task on task.entitykey = more_than_one_entry.entitykey 
inner join taskexec on taskexec.task_id = task.id order by task.entitykey asc;                   
END
$func$  LANGUAGE plpgsql;

But which the second function, I'm not able to return a table, created from looping through the results of the entity_with_multiple_taskexec function;

CREATE OR REPLACE  FUNCTION row_id_to_delete() 
    RETURNS TABLE(task_id varchar, taskexec_id varchar) AS 
$func$
DECLARE 
    entityrow RECORD;
    resultset RECORD;
BEGIN
        FOR entityrow IN SELECT entitykey FROM entity_with_multiple_taskexec() LOOP
            insert into resultset select task.id as task_id, taskexec.id as taskexec_id from task  
            inner join taskexec on taskexec.task_id = task.id where taskexec.entitykey = entityrow.entitykey order by taskexec.enddate desc offset 1 
        END LOOP;
    RETURN resultset;
END
$func$ LANGUAGE plpgsql;

This breaks with the following error

ERROR:  syntax error at or near "END"
LINE 12:   END LOOP;

I've tried different approaches. What would be a good solution to return the table?

user330315 · Accepted Answer · 2019-10-14 07:00:34Z

2

You don't need a loop, just join to the function as if it is a table.

There is also no need to use PL/pgSQL for this, a simple language sql function will be more efficient.

CREATE OR REPLACE  FUNCTION row_id_to_delete() 
    RETURNS TABLE(task_id varchar, taskexec_id varchar) AS 
$func$
  select task.id as task_id, taskexec.id as taskexec_id 
  from task  
    join taskexec on taskexec.task_id = task.id 
    join entity_with_multiple_taskexec() as mt on mt.entitykey = taskexec.entitykey
  order by taskexec.enddate desc 
  offset 1 
$func$ 
LANGUAGE sql;

answered Oct 14, 2019 at 7:00

user330315

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

Collectives™ on Stack Overflow

How to return a table from iteration in function with postgres 11

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