PostgreSQL: function to display columns in alphabetical order

Question

On PostgreSQL, I need to see the table's columns in alphabetical order, so I'm using the query:

SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'organizations' ORDER BY column_name ASC;

I use it a lot every day, so I want to create a function:

 CREATE OR REPLACE FUNCTION seecols(table_name text)
 RETURNS TABLE (column_name varchar, data_type varchar)
AS $func$
 DECLARE
 _query varchar;
 BEGIN
  -- Displays columns by alphabetic order
   _query := 'SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '''||table_name||''' ';

  RETURN QUERY EXECUTE _query;
 END;
$func$ LANGUAGE plpgsql;

But when I try:

SELECT seecols('organizations');

I'm getting:

 **structure of query does not match function result type**

I guess the line "RETURNS TABLE (column_name varchar, data_type varchar)" is wrongly defined. But since this is my first time using plpgsql, I don't know how to make it more dynamic.

Look at the data types for [information_schema.columns])postgresql.org/docs/current/infoschema-columns.html). They are not varchar. Probably easiest thing to do is : SELECT column_name::varchar, data_type::varchar FROM .... Otherwise: RETURNS TABLE (column_name information_schema.sql_identifier, data_type information_schema.sql_identifier) . — Adrian Klaver
– Adrian Klaver, Commented Jan 7, 2023 at 21:01
Btw, no need for dynamic sql and building a string for the query. just use RETURN SELECT column_name, data_type FROM information_schema.columns WHERE column.table_name = seecols.table_name; — Bergi
– Bergi, Commented Jan 8, 2023 at 0:09

Edouard · Accepted Answer · 2023-01-08 10:58:28Z

1

You don't need neither dynamic sql nor plpgsql here. Just embed your sql query into a sql function :

CREATE OR REPLACE FUNCTION seecols (IN t_name text, OUT column_name varchar, OUT data_type varchar)
  RETURNS setof record LANGUAGE sql AS $$
  SELECT column_name, data_type 
    FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_NAME = t_name 
   ORDER BY column_name ASC ;
$$ ;

see dbfiddle

answered Jan 8, 2023 at 10:58

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: function to display columns in alphabetical order

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related