17

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!

justanother1
  • 599
  • 3
  • 7
  • 17

2 Answers2

29

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.

Community
  • 1
  • 1
Matthew Wood
  • 16,017
  • 5
  • 46
  • 35
  • 1
    What should I declare the result_var as? – justanother1 May 17 '12 at 17:27
  • 1
    Never interpolate values into dynamic sql statements without using the appropriate quote_FOO() function or you open the door to injection attacks. – dbenhur May 17 '12 at 17:51
  • This is an internal facing database. – justanother1 May 17 '12 at 17:55
  • 1
    "Internal facing" means anyone with access to your network can own your database. – dbenhur May 17 '12 at 18:29
  • @debenhur Yup, agreed; also, "internal facing" doesn't mean ACCIDENTAL injection of a problematic identifier won't horribly break things. – Craig Ringer May 18 '12 at 02:35
  • 1
    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. – Matthew Wood May 18 '12 at 15:52
  • @dbenhur: If I'm reading the documentation correctly, the %L and %I format specifications in 9.1's format() function server the same purpose as quote_literal() and quote_identifier() and, yes, these methods should be used if one must do dynamic queries. – Matthew Wood May 18 '12 at 15:54
  • @MatthewWood that's correct, they serve the same purpose, but make for somewhat more concise and readable code. – dbenhur May 18 '12 at 17:02
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;
dbenhur
  • 20,008
  • 4
  • 48
  • 45
  • How do I make it return all of the columns? – justanother1 May 17 '12 at 18:12
  • You can `SELECT *` and declare `RETURNS SETOF RECORD`. Then the consumer has to understand the record tuple. http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions – dbenhur May 17 '12 at 18:20
  • And how do I give it the column definition list? Is there any way to dynamically pull this? – justanother1 May 17 '12 at 18:22
  • 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. – dbenhur May 17 '12 at 18:29
  • You should ideally be using `EXECUTE ... USING` in your example to handle those two insertions into the WHERE clause as query parameters. In your particular case it doesn't matter as they're hardcoded, but it's a good idea in general. – Craig Ringer May 18 '12 at 02:38
  • @j.gardner117 See `INFORMATION_SCHEMA.COLUMNS` http://www.postgresql.org/docs/current/static/information-schema.html – Craig Ringer May 18 '12 at 02:39
  • 1
    @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. – Craig Ringer May 18 '12 at 02:42