CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
Diaz Pradiananto
Diaz Pradiananto

Reputation: 457

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

i have a storerd procedure like 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 query like this :

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

or 

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

it getting error like this : ERROR: column "invoice_no" does not exist

but when i try execute with one colon like this :

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

the result is no row.

how i can get the data like this :

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

thanks .

UPDATE : If we want a certain column of table that 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));

Upvotes: 2

Views: 7523

Answers (2)

Gesugao-san
Gesugao-san

Reputation: 75

You can query all databases from the server and sort them according to your own database.

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableName';

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658707

Your function could 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 that's simpler with format()

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

%I escapes identifiers like quote_ident().

Major points:

  • You were bumping into the limitation of dynamic SQL that you cannot use parameters for identifiers. You have to build the query string with the column name and then execute it.

  • You can do that with values though. I demonstrate the use of the USING clause for EXECUTE. Also note the use of quote_ident(): prevents SQL injection and certain syntax errors.

  • I also largely simplified your function. [RETURN QUERY EXECUTE][3] makes your code shorter and faster. No need to loop if all you do is return the row.

  • I use named IN parameters, so you don't get confused with the $-notation in the query string. $1 and $2 inside the query string refer to the values provided in the USING clause, not to the input parameters.

  • I change to SELECT * as you have to return the whole row to match the declared return type anyway.

  • Last but not least: Be sure to consider what the manual has to say about functions declared SECURITY DEFINER.

RETURN TYPE

If you don't want to return the whole row, one convenient possibility is:

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

Then you don't have to provide a column definition list with every call and can simplify to:

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

Upvotes: 7

Related Questions