PostgreSQL: turn record type output into table when using own function

Question

I have worked out the following function

CREATE OR REPLACE FUNCTION lgis_overview(id_number int) /*id of object*/
        RETURNS TABLE(datatype text, object_number int, object_name text,output text, link text)
    AS
    $$
    BEGIN
        RETURN QUERY
    (select tags.datatype::text, objects.id::int, objects.name::text, lgis_getvalue(tags.datatype::text, foo.id::int)::text, lgis_objectname(output)
    from tags, objects,(select * from values
    where object_id = id_number) as foo

    where tags.id = foo.tag_id
    and objects.id = foo.object_id);
   
END;
    $$
LANGUAGE 'plpgsql' VOLATILE;

This function gives this output:

select * from lgis_overview(148830)

Unfortunatelly, when i use this function in this form, the output is a list of record types:

select lgis_overview(id)
from objects 
limit 10

In what (clean) way can I rewrite the function so that the output is a table, or how should I use the function so that the output is a table?

As a side note: this doesn't require PL/pgSQL and the language name is an identifier. It should not be enclosed in single quotes. That syntax is deprecated and support for it might be removed in a future version. Better use language plpgsql. — user330315
– user330315, Commented May 20, 2021 at 5:47

user330315 · Accepted Answer · 2021-05-20 05:39:01Z

3

Set returning functions should be used in the FROM clause (as you did in your first query).

If you need to pass it a column value, use a lateral join:

select ov.*
from objects as o
  cross join lateral lgis_overview(o.id) as ov
limit 10

answered May 20, 2021 at 5:39

user330315

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

Collectives™ on Stack Overflow

PostgreSQL: turn record type output into table when using own function

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