SQL string not getting evaluated PostgreSQL

Question

I have a SQL function in PostgreSQL which returns the Column names of the table dynamically.following is the function that returns me the list of column names ( formatted ).

FUNCTION

CREATE OR REPLACE FUNCTION falc_app.get_quoted_column_names_for_a_table(sel_table_name text)
  RETURNS TABLE(
    script text
  )
AS  
 $$
   select array_to_string(
    array(
      select CAST ('|| quote_nullable(' || (column_name) ||') || '   as text) as script
                    FROM information_schema.COLUMNS
        WHERE COLUMNS.table_schema = 'falc_app'
          AND COLUMNS.TABLE_NAME = sel_table_name
        ORDER BY COLUMNS.ordinal_position ), ''','' ')

 $$
LANGUAGE sql;

OUTPUT

'col_username','col_password',......

PROBLEM

When I am trying to select on this function using the below code it works fine.

select falc_app.get_column_names_for_a_table(usertable);

but when I try to use this inside another function's code what happens is it doesn't get resolved to column names rather It gets resolved to below output, is there any way to get the same behavior like when selecting the function using a select statement

|| quote_nullable(col_username) || ',' || quote_nullable(col_password) ||......

Please don't tag other RDBMS. This is clearly about PostgreSQL, so there's no need to confuse matter by tagging sql-server too. — Thom A
– Thom A ♦, Commented Feb 4, 2020 at 9:32

JGH · Accepted Answer · 2020-02-04 12:53:47Z

1

You can simplify the function to get rid of the concatenation.

Also the function does not return a table but a single text. It's up to you to change it.

CREATE OR REPLACE FUNCTION falc_app.get_quoted_column_names_for_a_table(sel_table_name text)
  RETURNS TABLE(
    script text
  )
AS  
 $$
    SELECT STRING_AGG(QUOTE_NULLABLE(column_name),',' ORDER BY COLUMNS.ordinal_position ) as script
    FROM information_schema.COLUMNS
    WHERE COLUMNS.table_schema = 'falc_app'
        AND COLUMNS.TABLE_NAME = sel_table_name;
 $$
LANGUAGE sql;

answered Feb 4, 2020 at 12:53

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

SQL string not getting evaluated PostgreSQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related