Postgres Dynamic Query Function
plpgsql, postgresql, sql
Solution
You cannot use a variable in place of an identifier like that. You need to do it with dynamic queries. It will look something like this:
EXECUTE 'SELECT * FROM ' || quote_ident(tname)
|| ' WHERE ' || quote_ident(cname) || ' NOT IN (''AK'',''CK'');'
INTO result_var;
If you are using PostgreSQL 9.1 or above, you can use the format() function which makes constructing this string much easier.
Problem
I need to create a function that will run a query and return the results with the table name and the column name being arugments given to the function. I currently have this: ``` CREATE OR REPLACE FUNCTION qa_scf(tname character varying, cname character varying) RETURNS SETOF INT AS $BODY$ BEGIN RETURN QUERY SELECT * FROM tname WHERE cname !='AK' AND cname!='CK'; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000; ``` This gives me the error "Relation 'tname' des not exist" when run. I'm new to function creating for Postgres, so any help is appreciated. I feel like the return int is wrong, but I don't know what else to put to make it return all columns for the rows returned. Thanks!