I came across a guy that wanted to get rows from a table by specifying
the table name and column ordinal number and nothing more. [Yes, this
is useless and violates relational model and SQL priniciples.]
My initial thoughts centered on using an array to snag each row and
pull out the column number I want, but I couldn't figure out how to
concatenate all the columns together without specifying them
individually.
Then, I whipped up some plpgsql:
CREATE OR REPLACE FUNCTION columnx(tablename text,columnindex integer)
RETURNS SETOF RECORD
AS $$
DECLARE
r RECORD;
colname TEXT;
BEGIN
SELECT INTO colname isc.column_name FROM information_schema.columns AS
isc WHERE tablename LIKE table_schema || '.' || table_name AND
columnindex=isc.ordinal_position;
RAISE NOTICE '%',colname;
FOR r IN EXECUTE 'SELECT ' || colname || ' FROM ' || tablename || ';'
LOOP
RETURN NEXT r;
END LOOP;
END;
$$ LANGUAGE 'plpgsql';
But running this gets me:
agentm=# select * from columnx('public.test',2);
ERROR: a column definition list is required for functions returning
"record"
agentm=# select * from columnx('public.test',2) as ret(a anyelement);
ERROR: column "a" has pseudo-type anyelement
agentm=# select * from columnx('public.test',2) as ret(a text);
NOTICE: b
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "columnx" line 8 at return next
agentm=# select * from columnx('public.test',2) as ret(a integer);
NOTICE: b
a
---
2
(1 row)
In the function, I don't know until I get to the information schema
what types I will be returning and I can't declare a variable then.
Making it explicit (as I do in the last command) is cheating because I
would want it to return whatever type that column is without manually
figuring that out.
Can this be done without resorting to an external SQL generation
programr? Does anyone have a good hack to share?
-M
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@xxxxxxxxxxxxxxxxxxxxx
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
