19

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!

Improve this question

2 Answers 2

Reset to default
30

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.

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

8 Comments

What should I declare the result_var as?
Never interpolate values into dynamic sql statements without using the appropriate quote_FOO() function or you open the door to injection attacks.
This is an internal facing database.
"Internal facing" means anyone with access to your network can own your database.
Just an FYI, I don't recommend doing this AT ALL. Dynamic queries that take table/column parameters are many times an indication of a bad design.
|
19

Table and column names can not be specified as parameters or variables without dynamically constructing a string to execute as a dynamic statement. Postgres has excellent introductory documentation about executing dynamic statements. It's important to properly quote identifiers and literals with quote_ident() or quote_literal(). The format() function helps clean up dynamic sql statement construction. Since you declare the function to return SETOF INTEGER, you should select the integer field you want, not *.

CREATE OR REPLACE FUNCTION qa_scf(tname text, cname text)
RETURNS SETOF INTEGER AS
$BODY$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT the_integer_field FROM %I WHERE %I NOT IN (%L,  %L)',
                                   tname,   cname,    'AK', 'CK'
  );
END;
$BODY$
LANGUAGE plpgsql;
Improve this answer

7 Comments

How do I make it return all of the columns?
You can SELECT * and declare RETURNS SETOF RECORD. Then the consumer has to understand the record tuple. wiki.postgresql.org/wiki/…
And how do I give it the column definition list? Is there any way to dynamically pull this?
Read that wiki link in more detail, it explains how to interpret a record result with the consumer. If you're really returning polymorphic results, that gets into more introspection and dynamic metaprogramming -- are you sure you want to go there? Go open another question exploring those issues.
@j.gardner117 You can also return a hstore instead of a RECORD; a hstore is basically a dynamic hash table. It won't perform as well and doesn't have strong data typing, but is easily dynamically introspected. I think some changes were made in PL/PgSQL recently to make it work better with dynamic RECORDs where column names etc are unknown until runtime, but I don't recall the details; check the documentation.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.