Postgres plsql return one column from table with dynamic type

34 Views Asked by At

I'd like to create function using pgplsql which takes table name and column name and returns this column as result. The main problem which I faced is dynamic type of this columns (might be int/float/numeric/timestamp/etc)

I created function which returns set of record

create or replace
function extract_column(
    _table_name text,
    _col_name text)
  returns setof record
  language plpgsql as
$func$
begin
    return QUERY execute format(
        'select %I FROM schema_name.%I',
        _col_name,
        _table_name
    );
end
$func$;

And it works if i provide schema while query

SELECT * FROM extract_column('my_table', 'column812')
    as t(
        data varchar,
    );

But I cannot provide this type dynamically from my application, so I prepared another function which returns data type of column by its name

create or replace
function get_type(table_name text, col_name text)
    returns regtype
language plpgsql as
$func$ 
declare column_type regtype;

begin 
    select
        data_type::regtype
from
        information_schema.columns
where
        table_name = table_name 
    and column_name = col_name
    into
    column_type;

return column_type;
end 
$func$;

But I cannot use it in my query

SELECT * FROM extract_column('my_table', 'column812')
    as t(
        data get_type('my_table', 'column812'),
    );

It throws error

SQL Error [42704]: ERROR: type "get_type" does not exist

What can I do to make this work?

0

There are 0 best solutions below