Hello all,

I am trying to create a stored function through ODBC connection,
But the parameter marker inside the function is not substituted.
In the following snippet, '?' is not substituted, and I got a syntax
error around it.
If I replace the '?' with '2', it is OK.
The psqlodbc I am using is 08.02.0400 together with unixODBC:2.2.12 on
OpenSUSE 10.3.
Is there a solution or workaround?

Thank you.

--------------
std::stringstream ss;
ss <<
"create or replace function plpgsql_setDocAttrs() returns
void as $$ "
"declare "
"pid integer; "
"begin "
"pid := ?; " // parameter marker
"end;"
"$$ language plpgsql volatile;"
"select plpgsql_setDocAttrs();";
// prepare statement handler
Stmt stmt();
// prepare the statement
stmt.prepare(ss.str());
stmt.bindNextParameter(
SQL_PARAM_INPUT,
SQL_C_LONG,
SQL_INTEGER,
0,
0,
&id,
0,
0);
stmt.execute();
---------------

ning

--
Sent via pgsql-odbc mailing list (pgsql-odbc [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

ning <mailxiening [at] gmail.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

--
Sent via pgsql-odbc mailing list (pgsql-odbc [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

On Thursday 16 April 2009 1:02:56 am ning wrote:
> Hello all,
>
> I am trying to create a stored function through ODBC connection,
> But the parameter marker inside the function is not substituted.
> In the following snippet, '?' is not substituted, and I got a syntax
> error around it.
> If I replace the '?' with '2', it is OK.
> The psqlodbc I am using is 08.02.0400 together with unixODBC:2.2.12 on
> OpenSUSE 10.3.
> Is there a solution or workaround?
>
> Thank you.
>
> --------------
> std::stringstream ss;
> ss <<
> "create or replace function plpgsql_setDocAttrs() returns
> void as $$ "
> "declare "
> "pid integer; "
> "begin "
> "pid := ?; " // parameter marker
> "end;"
> "$$ language plpgsql volatile;"
> "select plpgsql_setDocAttrs();";
> // prepare statement handler
> Stmt stmt();
> // prepare the statement
> stmt.prepare(ss.str());
> stmt.bindNextParameter(
> SQL_PARAM_INPUT,
> SQL_C_LONG,
> SQL_INTEGER,
> 0,
> 0,
> &id,
> 0,
> 0);
> stmt.execute();
> ---------------
>
> ning

Might want to take a look at this:
http://www.commandprompt.com/blogs/joshua_drake/2009/04/esca ping_data_madness/

--
Adrian Klaver
aklaver [at] comcast.net

--
Sent via pgsql-odbc mailing list (pgsql-odbc [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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 :=3D ' "
" || ? || "
"'; "
"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 :=3D ' || 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 :=3D 10; end;'
--------
Then "create function" statement is executed as
-------
create or replace function plpgsql_setDocAttrs() returns void as '
declare pid integer;begin pid :=3D 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.pgh.pa.us> wrote:
> ning <mailxiening [at] gmail.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. =C2=A0It would be
> a bug for the software to consider that to be a parameter marker.
> It's just a question mark.
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane
>

--
Sent via pgsql-odbc mailing list (pgsql-odbc [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

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-porti ng.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 :=3D ' ||
? || ';');",
SQL_NTS
);
---------
'function_eval': function name.
'pid integer': "declare" part of function_eval().
'pid :=3D ' || ? || ';': "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 functio=
n.
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=3D/com.i=
bm.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.org/msg 58796.html
But I cannot find anything about implementation in this field.

Best regards.
ning

-------log------------
sample=3D# 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 :=3D 'declare ';
sample$# func_body :=3D func_body || $2;
sample$#
sample$# func_body :=3D func_body || 'BEGIN ';
sample$# func_body :=3D func_body || $3;
sample$# func_body :=3D func_body || ' END;';
sample$#
sample$# func_cmd :=3D
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=3D# select function_factory('function_eval', 'pid integer;', 'pid
:=3D ' || 10 || ';');
function_factory
------------------

(1 row)

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

(1 row)

sample=3D#
------------------

On Fri, Apr 17, 2009 at 2:52 PM, ning <mailxiening [at] gmail.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 <<
> =C2=A0 "create or replace function plpgsql_setDocAttrs() returns void as =
' "
> =C2=A0 "declare "
> =C2=A0 "pid integer; "
> =C2=A0 "begin "
> =C2=A0 "pid :=3D ' "
> =C2=A0 " || ? || "
> =C2=A0 "'; "
> =C2=A0 "end;"
> =C2=A0 "' language plpgsql volatile;"
> ----------
>
> When I bind a variable, with value 10, to ?, the string after substitutio=
n is
> ----------
> create or replace function plpgsql_setDocAttrs() returns void as
> 'declare pid integer;begin pid :=3D ' =C2=A0|| 10 || '; end;' language
> plpgsql volatile;
> ----------
> I got the following error:
> ERROR: =C2=A0syntax 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 =C2=A0function body is firstly concatenated to
> ---------
> 'declare pid integer;begin pid :=3D 10; end;'
> --------
> Then "create function" statement is executed as
> -------
> create or replace function plpgsql_setDocAttrs() returns void as '
> declare pid integer;begin pid :=3D 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.pgh.pa.us> wrote:
>> ning <mailxiening [at] gmail.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. =C2=A0It would be
>> a bug for the software to consider that to be a parameter marker.
>> It's just a question mark.
>>
>> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0regards, tom lane
>>
>

--
Sent via pgsql-odbc mailing list (pgsql-odbc [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc