current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
2

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!

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
What should I declare the result_var as? –  j.gardner117 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. –  j.gardner117 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
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
up vote 9 down vote

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;
share|improve this answer
    
How do I make it return all of the columns? –  j.gardner117 May 17 '12 at 18:12
    
You can SELECT * and declare RETURNS SETOF RECORD. Then the consumer has to understand the record tuple. wiki.postgresql.org/wiki/… –  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? –  j.gardner117 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 2:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.