Re: Bound parameter is not substituted

Hello all,

I finally found a way to create function dynamically from client side
through ODBC connection by referring to
http://www.postgresql.org/docs/8.3/interactive/plpgsql-porting.html
example 38-6: definiton of function cs_update_referrer_type_proc()

The psql command line log is pasted below for reference.
First, a stored function function_factory() needs to be installed on
server side,
which is used to created function dynamically.
Please refer to log below for definition of funciton_factory().

Second, we call function_factory() from client side to create a new function
whose name, "declare" part and "begin-end" part are dynamically constructed.
---------
SQLPrepare(
hstmt,
"select function_factory('function_eval', 'pid integer;', 'pid := ' ||
? || ';');",
SQL_NTS
);
---------
'function_eval': function name.
'pid integer': "declare" part of function_eval().
'pid := ' || ? || ';': "begin-end" part of function_eval(), "?" can be
substituted.

Third, execute function_eval()
--------
SQLPrepare(
hstmt,
"select function_eval();",
SQL_NTS
);
--------

I know it's hard to use and maybe not the best solution for dynamic function.
Any idea for simpler solution will be appreciated.

In fact, if inline SQL PL were supported as DB2:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.sql.doc/doc/r0020490.htm
The solution would be straightforward.

Someone talked about Inline PL/pgSQL in 2005:
http://www.mail-archive.com/pgsql-hackers(at)postgresql(dot)org/msg58796.html
But I cannot find anything about implementation in this field.

Best regards.
ning

-------log------------
sample=# CREATE OR REPLACE FUNCTION function_factory(text, text, text)
RETURNS void AS $func$
sample$# DECLARE
sample$# func_body text;
sample$# func_cmd text;
sample$# BEGIN
sample$# func_body := 'declare ';
sample$# func_body := func_body || $2;
sample$#
sample$# func_body := func_body || 'BEGIN ';
sample$# func_body := func_body || $3;
sample$# func_body := func_body || ' END;';
sample$#
sample$# func_cmd :=
sample$# 'CREATE OR REPLACE FUNCTION ' || $1 ||
sample$# '() RETURNS void AS '
sample$# || quote_literal(func_body)
sample$# || ' LANGUAGE plpgsql;' ;
sample$#
sample$# EXECUTE func_cmd;
sample$# END;
sample$# $func$ LANGUAGE plpgsql;
CREATE FUNCTION
sample=# select function_factory('function_eval', 'pid integer;', 'pid
:= ' || 10 || ';');
function_factory
------------------

(1 row)

sample=# select function_eval();
function_eval
---------------

(1 row)

sample=#
------------------

On Fri, Apr 17, 2009 at 2:52 PM, ning <mailxiening(at)gmail(dot)com> wrote:
> Thank you very much for your reply.
>
> Now I understand the cause of the error, but I seems funciton body
> cannot be constructed dynamically.
> I tried:
> -----------
> ss <<
>   "create or replace function plpgsql_setDocAttrs() returns void as ' "
>   "declare "
>   "pid integer; "
>   "begin "
>   "pid := ' "
>   " || ? || "
>   "'; "
>   "end;"
>   "' language plpgsql volatile;"
> ----------
>
> When I bind a variable, with value 10, to ?, the string after substitution is
> ----------
> create or replace function plpgsql_setDocAttrs() returns void as
> 'declare pid integer;begin pid := '  || 10 || '; end;' language
> plpgsql volatile;
> ----------
> I got the following error:
> ERROR:  syntax error at or near "||"
>
> String concatenation operator "||" is not allowed in "create function"
> statement, which breaks syntax.
> Is there any way to force string concatenation before function definition?
> So that the  function body is firstly concatenated to
> ---------
> 'declare pid integer;begin pid := 10; end;'
> --------
> Then "create function" statement is executed as
> -------
> create or replace function plpgsql_setDocAttrs() returns void as '
> declare pid integer;begin pid := 10; end;' language plpgsql volatile;
> ------
>
> Any suggestion or idea is appreciated.
>
> Best regards.
>
> ning
>
> On Thu, Apr 16, 2009 at 11:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> ning <mailxiening(at)gmail(dot)com> writes:
>>> I am trying to create a stored function through ODBC connection,
>>> But the parameter marker inside the function is not substituted.
>>
>> You've written a question mark inside a string literal.  It would be
>> a bug for the software to consider that to be a parameter marker.
>> It's just a question mark.
>>
>>                        regards, tom lane
>>
>