Postgres pl / pgsql ERROR: column "column_name" does not exist

Question

I have a storage procedure as shown below

CREATE FUNCTION select_transactions3(text, text, int) RETURNS SETOF transactions AS $body$ DECLARE rec transactions%ROWTYPE; BEGIN FOR rec IN (SELECT invoice_no, trans_date FROM transactions WHERE $1 = $2 limit $3 ) LOOP RETURN NEXT rec; END LOOP; END; $body$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER; 

when I execute the request as follows:

 select * from select_transactions3("invoice_no", '1103300105472',10); 

or

 select * from select_transactions3(invoice_no, '1103300105472',10); 

an error occurs: ERROR: column "invoice_no" does not exist

but when I try to execute with a single colon, like this:

 select * from select_transactions3('invoice_no', '1103300105472',10); 

as a result there is no line.

How can I get this data:

  invoice_no | trans_date ---------------+------------------------- 1103300105472 | 2011-03-30 12:25:35.694 

thanks.

UPDATE: if we need a specific column of the table we want to show

 CREATE FUNCTION select_to_transactions14(_col character varying, _val character varying, _limit int) RETURNS SETOF RECORD AS $$ DECLARE rec record; BEGIN FOR rec IN EXECUTE 'SELECT invoice_no, amount FROM transactions WHERE ' || _col || ' = $1 LIMIT $2' USING _val, _limit LOOP RETURN NEXT rec; END LOOP; END; $$ LANGUAGE plpgsql; 

to get the result:

 SELECT * FROM select_to_transactions14( 'invoice_no', '1103300105472',1) as ("invoice_no" varchar(125), "amount" numeric(12,2)); 

Answer 1

Your function might look like this:

 CREATE FUNCTION select_transactions3(_col text, _val text, _limit int) RETURNS SETOF transactions AS $BODY$ BEGIN RETURN QUERY EXECUTE ' SELECT * FROM transactions WHERE ' || quote_ident(_col) || ' = $1 LIMIT $2' USING _val, _limit; END; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER; 

In PostgreSQL 9.1 or later, simpler with format()

 ... RETURN QUERY EXECUTE format(' SELECT * FROM transactions WHERE %I = $1 LIMIT $2', _col) USING _val, _limit; ... 

%I allocates identifiers of type quote_ident() .

Basic moments:

• You have encountered a dynamic SQL constraint that cannot use parameters for identifiers. You must build a query string with a column name and then execute it.

• You can do it with values. I demonstrate using the USING for EXECUTE . Also note the use of quote_ident() : prevents SQL injection and some syntax errors.

• I also greatly simplified your function. [RETURN QUERY EXECUTE][3] makes your code shorter and faster. No need to loop if everything you do returns a string.

• I use the parameters of the name IN , so you are not confused with $ -notation in the query string. $1 and $2 inside the query string refer to the values ​​specified in the USING , and not to the input parameters.

• I am switching over to SELECT * , since you still need to return the entire string so that it matches the declared return type.

• Last but not least, be sure to consider what the manual should say about the declared SECURITY DEFINER functions.

RETURN TYPE

If you do not want to return the entire string, one convenient option:

 CREATE FUNCTION select_transactions3(_col text, _val text, _limit int) RETURNS TABLE (invoice_no varchar(125), amount numeric(12,2) AS ... 

Then you do not need to provide a column definition list with each call and can simplify:

 SELECT * FROM select_to_transactions3('invoice_no', '1103300105472', 1);