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?