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.
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).RETURN SELECT column_name, data_type FROM information_schema.columns WHERE column.table_name = seecols.table_name;