CODEWITHSUNDEEP
postgresqlselectfield
Jsy
Jsy

Reputation: 53

How to get the fields' information from a SQL statement in PostgreSQL?

I want to get fields' description from a SQL statement. That is, I want create a function, use a sql statement as the input parameter, returns a jsonb value to decribe the fields' information.

If I know the table name, I can do this:

SELECT column_name, data_type
FROM information_schema."columns"
WHERE "table_name"='TABLE-NAME'

But if I only have the select sql statement, such as select 0::bigint as fid, 'A'::text as fname;,how can I get the column_name and data_type In a function.

Below is the structure for then function when the I know the table name:

create or replace function public.getfieldsfromtable(vtable text)
    returns jsonb
    language 'plpgsql'
    cost 100
    volatile 
as $body$ declare
    jfields jsonb='[]'::jsonb;
begin
    select 
        array_to_json(array_agg(row_to_json(t))) 
    into jfields
    from (
        select column_name, data_type
        from information_schema."columns"
        where "table_name"=vtable
    ) t;
    return jfields;
end;
$body$;

But What Can I Do If I Just Have A Select SQL Statement?

create or replace function public.getfieldsfromsql(vsql text)
    returns jsonb
    language 'plpgsql'
    cost 100
    volatile 
as $body$ declare
    jfields jsonb='[]'::jsonb;
begin
    ... what can i do?
    return jfields;
end;
$body$;

Upvotes: 1

Views: 79

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45835

It can be easy, if you do it in C extension, but it is almost impossible in PL/pgSQL. For stored procedures, there is not an API, how to detect structure of result without query execution.

Upvotes: 2

Related Questions