Thread: Dynamic SQL Syntax help please
<div dir="ltr">I created a simple function that i want to use another function (in the same schema) that will be used tocreate a sql statement. I get the following error when i try this:<br /><br /><font size="2"><i style="color: rgb(204,0, 0);">ERROR: syntax error at or near "EXEC" at character 1<br /> QUERY: EXEC SQL EXECUTE $1 INTO $2 <br />CONTEXT: SQL statement in PL/PgSQL function "fcn_population" near line 10<br /><br />********** Error **********<br /><br/>ERROR: syntax error at or near "EXEC"<br /> SQL state: 42601<br />Context: SQL statement in PL/PgSQL function "fcn_population"near line 10</i></font><br /><br />Here is my function:<br /><br />CREATE OR REPLACE FUNCTION fcn_population(p_datedate, p_where varchar)<br /> RETURNS numeric AS $$<br /><br />DECLARE<br /> v_total_pop numeric :=0;<br /> v_stmt varchar(1024);<br /> v_where varchar(256) := 'where m.date_created < '||p_date||' '||+ integer'1'||' ';<br /> <br /><br />BEGIN<br /> v_stmt := fcn_gen_statement(p_type, v_where,0);<br /> EXEC SQL EXECUTE v_stmt INTO v_total_pop;<br /> return v_total_pop;<br />END;<br />$$ LANGUAGE plpgsql;<br/><br /><br /></div>
On Thu, 4 Sep 2008 13:41:16 -0700 "Ruben Gouveia" <rubes7202@gmail.com> wrote: > I created a simple function that i want to use another function > (in the same schema) that will be used to create a sql statement. > I get the following error when i try this: > > *ERROR: syntax error at or near "EXEC" at character 1 > QUERY: EXEC SQL EXECUTE $1 INTO $2 > CONTEXT: SQL statement in PL/PgSQL function "fcn_population" near > line 10 > EXEC SQL EXECUTE v_stmt INTO v_total_pop; Could it be you're mixing MS SQL (Oracle?) syntax with pgplsql syntax EXECUTE v_stmt INTO v_total_pop; http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Ivan Sergio Borgonovo http://www.webthatworks.it
<div dir="ltr">thank you Sergio.<br /><br />I decided to use <br /><br />EXECUTE v_stmt into v_total_pop;<br /><br /><br/><div class="gmail_quote">On Thu, Sep 4, 2008 at 2:00 PM, Ivan Sergio Borgonovo <span dir="ltr"><<a href="mailto:mail@webthatworks.it">mail@webthatworks.it</a>></span>wrote:<br /><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">On Thu,4 Sep 2008 13:41:16 -0700<br /> "Ruben Gouveia" <<a href="mailto:rubes7202@gmail.com">rubes7202@gmail.com</a>>wrote:<br /><br /> > I created a simple function that i wantto use another function<br /> > (in the same schema) that will be used to create a sql statement.<br /> > I getthe following error when i try this:<br /> ><br /> > *ERROR: syntax error at or near "EXEC" at character 1<br />> QUERY: EXEC SQL EXECUTE $1 INTO $2<br /> > CONTEXT: SQL statement in PL/PgSQL function "fcn_population" near<br/> > line 10<br /><br /></div><div class="Ih2E3d">> EXEC SQL EXECUTE v_stmt INTO v_total_pop;<br /><br /></div>Couldit be you're mixing MS SQL (Oracle?) syntax with pgplsql syntax<br /><div class="Ih2E3d"><br /> EXECUTE v_stmtINTO v_total_pop;<br /><br /></div><a href="http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" target="_blank">http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN</a><br /><br/> --<br /> Ivan Sergio Borgonovo<br /><a href="http://www.webthatworks.it" target="_blank">http://www.webthatworks.it</a><br/><font color="#888888"><br /><br /> --<br /> Sent via pgsql-sql mailinglist (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /></div>