[SQL] EXECUTE with a prepared plan and NULL
Hello! I'm trying to run this code in one of my stored procedures s := 'execute prepared_plan( ' || id_engine || ',' || id_search || ',' || id_rsite || ')'; execute s; where "prepared_plan" is a statement defined before with PREPARE. The thing is, that if i one of the parameters is NULL, then string s becomes NULL also ( because NULL||"any string" = NULL) and as a result i get this error - ERROR: cannot EXECUTE a null querystring CONTEXT: PL/pgSQL function "factorize" line 148 at execute statement how this can be solved? (I want to send NULL to the prepared statement when one of the parameters is null) Another small question - Why when i remove the "execute" from the beginning of s and run it like this - s := 'prepared_plan( ' || id_engine || ',' || id_search || ',' || id_rsite || ')'; execute s; then i get a syntax error? Thank you!
Re: [SQL] How to excute dynamically a generated SQL command?
On Saturday 03 January 2009 5:57:32 pm John Zhang wrote:
> Hi the list,
>
> Referring to the PostgreSQL 8.3 documentation " 38.5.4. Executing Dynamic
> Commands ", the command for executing a dynamic command is:
> EXECUTE command-string [ INTO [STRICT] target ];
>
>
> I am to execute an sql statement created dynamically, which is represented
> in a variable sSql.
> Here is an example:
> sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES
> ('.tif', lo_import( E''C:\\HM\\Data\\Flightmap.tif'');'
You are missing a ')' Should be
VALUES ('.tif', lo_import( E''C:\\HM\\Data\\Flightmap.tif''));';
> EXECUTE sSQL;
>
> It raises the error as:
> ERROR: syntax error at end of input
> LINE 1: ...E'C:\\HM\\Data\\Flightmap.tif')
> ^
>
> I would appreciate a lot if you offer your input. Thanks a lot.
>
> John
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] EXECUTE with a prepared plan and NULL
"sergey kapustin" writes: > The thing is, that if i one of the parameters is NULL, then string s becomes > NULL also ( because NULL||"any string" = NULL) and as a result i get this > error - > ERROR: cannot EXECUTE a null querystring quote_nullable() might help. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
