PostgreSQL: there is no parameter $1 in dblink query

Question

Im currently using PostgreSQL 9.3 and Im trying to work on this function which connects to another database (esms) and the result of the said function will be compared and used in another function within another database (seis).

CREATE OR REPLACE FUNCTION lowest_grade_query(varchar) RETURNS numeric AS $$
DECLARE
test numeric(3,2);
BEGIN

 PERFORM dblink_connect_u('esms_ref', 'dbname=esms user=postgres password=postgres');

 SELECT * INTO test FROM dblink('esms_ref', 'SELECT MAX(to_number(CASE WHEN grade IN (''DRP'', ''INC'')
                                          THEN ''5.00'' 
                                          ELSE grade END, ''9D99'')) 
                         FROM registration
                         WHERE studid=$1') AS lowest_grade(grade numeric(3,2));

 PERFORM dblink_disconnect('esms_ref');

 RETURN test;

END;
$$ LANGUAGE plpgsql;

But when I try this

SELECT lowest_grade_query('2014-0035');

I get this error:

ERROR:  there is no parameter $1
CONTEXT:  Error occurred on dblink connection named "esms_ref": could not execute query.
SQL statement "SELECT *           FROM dblink('esms_ref', 'SELECT MAX(to_number(CASE WHEN grade IN (''DRP'', ''INC'')
                                          THEN ''5.00'' 
                                          ELSE grade END, ''9D99'')) 
                         FROM registration
                         WHERE studid=$1') AS lowest_grade(grade numeric(3,2))"

Where did it go wrong?

Erwin Brandstetter · Accepted Answer · 2015-02-15 05:14:48Z

2

You forgot to transform the parameter $1 for the remote query.
Your query is:

 dblink('esms_ref', 'SELECT * FROM registration WHERE studid=$1')

What is the value for parameter $1? Queries executed by dblink have their own namespace, plpgsql variables are not implicitly propagated there. $1 in the plpgsql function body does not refer to the same as $1 in a dblink (or other dynamic) query. You have to merge the parameter into the query string explicitly:

dblink('esms_ref', 
   format('SELECT * FROM registration WHERE studid=%L', $1))

edited Feb 15, 2015 at 5:14

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

answered Feb 14, 2015 at 8:01

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: there is no parameter $1 in dblink query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related