asked on

Return table through a function in Postgres.

We wanted to return table (with data) through a function in Postgres , we have been selecting data from table through return query and in function return signature as set of Type variable combination but it is time consuming when we return large volume of data . Could you please advise how to return table directly.

Bill Prew

Have you tried the approach shown in this tutorial?

PL/pgSQL Function Returns A Table

»bp

ASKER

Hi , We tried this earlier, but this approach it seems we can not we keep return table column names same as select query out put column names .

With type we can out put same column names . Any work around?

ASKER

Please find below UDF code, table have some data.
This is so weird , why i can not have same column names as return select query fields. Please find below error, if I change column names in RETURNS TABLE signature, then this function executes without any error. Is there any way i can keep same column names as return query select columns which i needed to output data with headers.

CREATE OR REPLACE FUNCTION public.get_country (id int)
RETURNS TABLE (
country_id integer,
country character varying(50),
last_update timestamp without time zone
)
AS $$
BEGIN
RETURN QUERY
SELECT
country_id,
country,
last_update
FROM
public.country where country_id=id;
END; $$
LANGUAGE 'plpgsql';

Ran as below:

select * from public.get_country (4) ;

ERROR: column reference "country_id" is ambiguous
LINE 2: country_id,
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: SELECT
country_id,
country,
last_update
FROM
public.country where country_id=id
CONTEXT: PL/pgSQL function get_country(integer) line 3 at RETURN QUERY
********** Error **********

ERROR: column reference "country_id" is ambiguous
SQL state: 42702
Detail: It could refer to either a PL/pgSQL variable or a table column.
Context: PL/pgSQL function get_country(integer) line 3 at RETURN QUERY

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks, it is working with table Alias. I don't know how it is different but it is working.

ASKER

Thanks for your help.

Bill Prew

Welcome.

»bp