How to create a function in postgresql that accepts array of parameters and returns a table

Question

Here is how my function is looks:

create or replace function datafabric.test(psi_codes text[])
returns table (asset character varying (255), parent_asset character varying (255))
as
$$
select asset, parent_asset 
from americas.asset a
left join americas.prod_int p on a.row_id = p.row_id
where root_asset_id in (select root_asset_id from americas.asset where p.name =  ANY($1))
$$ LANGUAGE 'SQL' VOLATILE;

However, the problem is that I am getting this

ERROR: function datafabric.test() does not exist SQL state: 42883 DURING the CREATION OF THE FUNCTION.

Please note that this function works. However, I want to output the results on pgadmin screen. I am not able to do that now.

Please help. I am using postgresql 8.3 version.

What's the intention of the alter function? It's invalid syntax. And please don't put the language name in single quotes - it's an identifier. — user330315
– user330315, Commented May 13, 2016 at 20:26
You are right, thanks. I removed single quotes from language. It now works, but here is what I get now. ERROR: query has no destination for result data SQL state: 42601 — Vids
– Vids, Commented May 13, 2016 at 21:00
I want to print the results in pgadmin client..similar to how a select statment would output.. — Vids
– Vids, Commented May 13, 2016 at 21:01
Why the outdated version 8.3? Your question is inconclusive; not enough information. Please table-qualify all columns in your query or provide table definitions. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 14, 2016 at 0:43

Ezequiel Tolnay · Accepted Answer · 2016-05-14 02:01:54Z

1

PostgreSQL 8.3 doesn't support RETURNS TABLE for functions. You also have to specify the language of a function without the quotes.

You can achieve a similar behaviour through the following:

create or replace function 
       datafabric.test(psi_codes text[],
                       OUT asset character varying (255),
                       OUT parent_asset character varying (255))
       RETURNS SETOF RECORD as $$
select asset, parent_asset 
from americas.asset a
left join americas.prod_int p on a.row_id = p.row_id
where root_asset_id in (select root_asset_id from americas.asset where p.name =  ANY($1))
$$ LANGUAGE SQL VOLATILE;

answered May 14, 2016 at 2:01

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

How to create a function in postgresql that accepts array of parameters and returns a 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