Return list of columns via function in PostgreSQL

Question

I'd like a function that returns a text string of comma-separated column headers for a given table name. the code below compiles fine and does not produce any errors, however I can't get it to return anything other than NULL for tables that do and do not exist.

CREATE OR REPLACE FUNCTION myschema.return_column_list(tab_name TEXT)
    RETURNS text AS
    $BODY$
        DECLARE
            one_column record;
            column_list TEXT;
        BEGIN

            FOR one_column IN
                SELECT column_name FROM information_schema.columns
                WHERE table_schema = 'myschema'
                AND table_name = format('%s', tab_name)
            LOOP
                column_list = column_list || one_column || ',';
            END LOOP;

            column_list = left(column_list,length(column_list) - 1);
            RETURN column_list;

        END;
    $BODY$ LANGUAGE 'plpgsql';

Update:

finally got it working by changing the DECLARE section from

DECLARE
    one_column record;
    column_list TEXT;

to

DECLARE
    one_column record;
    column_list TEXT = '';

as per the first example here. Spent the whole morning trying to figure this one out, can anyone explain why I have to do this?

one_column is a record that contains a single attribute. To get the actual column_name value you have to use one_column.column_name. — user330315
– user330315, Commented Sep 20, 2017 at 14:43
column_list TEXT; initialized column_list to null. Concatenating values to a null value yields null — user330315
– user330315, Commented Sep 20, 2017 at 14:44
@a_horse_with_no_name it was the variable initialisation bit that threw me! makes perfect sense once you know - will read up on it. — Alex
– Alex, Commented Sep 20, 2017 at 15:05

Lukasz Szozda · Accepted Answer · 2017-09-20 14:52:42Z

1

There is no need for loop at all. You could simply use string_agg:

SELECT STRING_AGG(column_name, ',')
FROM information_schema.columns
WHERE table_schema = 'public'
 AND  table_name =  'testx';

You could use it as subquery in your code.

Rextester Demo

Or if you need a function:

CREATE OR REPLACE FUNCTION myschema.return_column_list(tab_name TEXT)
RETURNS text AS
$BODY$
BEGIN
  RETURN (SELECT string_agg(column_name, ',') FROM information_schema.columns
                    WHERE table_schema = 'myschema'
                    AND table_name = format('%s', tab_name));
END;
BODY$ LANGUAGE 'plpgsql';

Rextester Demo 2

As for variable initialization. Default value is NULL and NULL concatenated to column_name is still NULL.

SELECT NULL || 'a';
-- NULL

edited Sep 20, 2017 at 14:52

answered Sep 20, 2017 at 14:42

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

Comments

Andreea Craciun · Accepted Answer · 2017-09-20 14:45:42Z

1

I am not 100% sure I understand your question, but that is the initial value:

 DECLARE variable_name [ CONSTANT ] datatype [ NOT NULL ] [ { DEFAULT | := } initial_value ]

You declare the not null situation basically there, in case your function will return nothing, for example no records. For string (text in your case) it is ''.

If null, you also cannot add to it, for example concatenate with the new values!

answered Sep 20, 2017 at 14:45

Andreea Craciun

3101 silver badge6 bronze badges

Collectives™ on Stack Overflow

Return list of columns via function in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related