Prepared statements in functions #273

Closed
satya-d opened this Issue Feb 20, 2013 · 3 comments

Comments

3 participants

satya-d commented Feb 20, 2013

I created following function. It gets error when run this function.

-- Function: sp_getmessages(character varying, character varying, integer, character varying)

-- DROP FUNCTION sp_getmessages(character varying, character varying, integer, character varying);

CREATE OR REPLACE FUNCTION sp_getmessages(strinstancename character varying, strqname character varying, nlmt integer, xparams character varying)
RETURNS void AS
$BODY$

DECLARE
flag VARCHAR(50);
nLimit INTEGER;
nLimit_1 INTEGER;
nLimit_2 INTEGER;
v_nLimit integer;
v_nLimit_1 integer;
v_nLimit_2 integer;
v_InstanceName VARCHAR(1024);
v_Qvalue varchar(1024);
v_flag INTEGER;

BEGIN
IF (nLmt != 0) THEN
v_nLimit := 5000;
ELSE
v_nLimit := 1000;
select v_nLimit;
END IF;
v_nLimit_1 := ROUND((CAST(75 AS decimal)/100)*v_nLimit);
v_nLimit_2 := v_nLimit -v_nLimit_1;
v_InstanceName := TRIM(strInstanceName);
v_Qvalue := strQname;

v_flag := floor(EXTRACT(EPOCH FROM now()));

PREPARE fooplan1(integer,char,char,integer) AS
update messaging set status =
$1, instance_name = $2 where message_id in ( select message_id from messaging where status = 0 and queue_name = $3 limit $4) ;

 EXECUTE fooplan1(v_flag, v_InstanceName, v_Qvalue , v_nLimit_1);

PREPARE fooplan2(date,char,char,integer) AS
update messaging set status = $1, instance_name = $2 where message_id in (select message_id from messaging where status = 0 and queue_name = $3 limit $4) ;
EXECUTE fooplan2(v_flag, v_InstanceName, v_Qvalue , v_nLimit_2);

DEALLOCATE fooplan1;
DEALLOCATE fooplan2;
prepare sel as select message_string,status
from messaging
where instance_name=$1
and queue_name=$2
and status=$3
limit $4;
EXECUTE sel(v_InstanceName, v_Qvalue, v_flag, v_nLimit);
END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION sp_getmessages(character varying, character varying, integer, character varying)
OWNER TO postgres;


select sp_getMessages('Email_165', '7' ,1000 , '');

I faced following error

ERROR: function fooplan1(integer, character varying, character varying, integer) does not exist
LINE 1: SELECT fooplan1(v_flag, v_InstanceName, v_Qvalue , v_nLimit_...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT fooplan1(v_flag, v_InstanceName, v_Qvalue , v_nLimit_1)
CONTEXT: PL/pgSQL function sp_getmessages(character varying,character varying,integer,character varying) line 33 at EXECUTE statement

********** Error **********

ERROR: function fooplan1(integer, character varying, character varying, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Context: PL/pgSQL function sp_getmessages(character varying,character varying,integer,character varying) line 33 at EXECUTE statement

Owner

brianc commented Feb 23, 2013

I dont understand exactly where the error is happening. Can you give runnable code sample to reproduce?

polobo commented Nov 23, 2013

User Error? Not proven out but the prepares are using "char" for the middle arguments while the caller(s) are providing "varchar" values.

polobo commented Nov 23, 2013

It is user error but because the SQL version of execute cannot be used inside a pl/pgsql function per PostgreSQL documentation.

http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

brianc closed this Mar 19, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment