Обсуждение: Any way to execute ad-hoc pl/pgsql?
One of our developers asked me, "is there any way to execute arbitrary plpgsql"? By that I beleive he means: is there some way to execute ad-hoc pl/pgsql code without creating a stored procedure or a function? I believe MS SQL Server can do this - has any one heard of some sort of command shell to do this for PG? (I suppose one possibility would be something that created a temporary stored proc to execute the code, then cleaned up after itself.) Carlo
On Fri, May 1, 2009 at 6:25 AM, Carlo Stonebanks <stonec.register@sympatico.ca> wrote: > One of our developers asked me, "is there any way to execute arbitrary > plpgsql"? By that I beleive he means: is there some way to execute ad-hoc > pl/pgsql code without creating a stored procedure or a function? > > I believe MS SQL Server can do this - has any one heard of some sort of > command shell to do this for PG? > > (I suppose one possibility would be something that created a temporary > stored proc to execute the code, then cleaned up after itself.) Sounds like you need anonymous blocks, which no, PostgreSQL doesn't support. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On 2009-05-01, Carlo Stonebanks <stonec.register@sympatico.ca> wrote: > One of our developers asked me, "is there any way to execute arbitrary > plpgsql"? By that I beleive he means: is there some way to execute ad-hoc > pl/pgsql code without creating a stored procedure or a function? no. arbitrary SQL is no problem, arbitrary plpgsql must be declared as a function so that it can be compiled and run. (compiling is automatic) inside a function execute can be used but not all plpgsql can be executed (but, again, you can use execute to define and run a new function) > I believe MS SQL Server can do this - has any one heard of some sort of > command shell to do this for PG? > (I suppose one possibility would be something that created a temporary > stored proc to execute the code, then cleaned up after itself.) You seem to be wanting something like "anonymous functions". postgres doesn't do them yet. It may in the future, or they may be incompatible with the design.
Carlo Stonebanks wrote: > (I suppose one possibility would be something that created a temporary > stored proc to execute the code, then cleaned up after itself.) Yep, that's what I do - CREATE FUNCTION fred() RETURNS blah AS $$ $$ LANGUAGE 'plpgsql'; SELECT fred(); DROP FUNCTION fred(); I've only needed it a couple of times in really complex data conversion and import routines, though. It'd be really quite nice to have CREATE TEMPORARY FUNCTION even if Pg didn't have true anonymous blocks (which IMO encourage unnecessary procedural style coding). -- Craig Ringer
You can wrap a temporary function in a script and call it this way:
keaton:811:~$more my_shell_script.sh
#!/bin/bash
OS=`uname -s`
PSQL="/usr/bin/psql"
USERNAME="postgres"
export PGPASSWORD="${PASSWORD}"
DATABASE="mydatabase"
${PSQL} "${DATABASE}" -U "${USERNAME}" << EOF
BEGIN;
CREATE OR REPLACE FUNCTION tmp_mxl_db_convert1 (VARCHAR) RETURNS INT AS '
DECLARE
s_table ALIAS FOR \$1;
tday VARCHAR(128);
tmonth VARCHAR(128);
tqtr VARCHAR(128);
tbegin TIMESTAMP WITH TIME ZONE;
tend TIMESTAMP WITH TIME ZONE;
n_ret INTEGER;
BEGIN
-- 2 quarters ago
tqtr := to_char(now() - interval ''6 months'', ''YYYY"q"Q'');
tbegin := date_trunc(''quarter'', now() - ''6 months''::interval);
tend := date_trunc(''quarter'', now() - ''3 months''::interval);
n_ret := tmp_mxl_threat_convert2(s_table, tqtr, tbegin, tend);
-- last quarter
tqtr := to_char(now() - interval ''3 months'', ''YYYY"q"Q'');
tbegin := tend;
tend := date_trunc(''quarter'', now());
n_ret := tmp_mxl_threat_convert2(s_table, tqtr, tbegin, tend);
.
.
More StoredProc / Function Code here
.
.
RETURN 0;
END;
' LANGUAGE 'plpgsql';
drop function tmp_mxl_threat_convert1 (VARCHAR);
END;
EOF
-Keaton
On 4/30/09 11:25 PM, "Carlo Stonebanks" <stonec.register@sympatico.ca> wrote:
keaton:811:~$more my_shell_script.sh
#!/bin/bash
OS=`uname -s`
PSQL="/usr/bin/psql"
USERNAME="postgres"
export PGPASSWORD="${PASSWORD}"
DATABASE="mydatabase"
${PSQL} "${DATABASE}" -U "${USERNAME}" << EOF
BEGIN;
CREATE OR REPLACE FUNCTION tmp_mxl_db_convert1 (VARCHAR) RETURNS INT AS '
DECLARE
s_table ALIAS FOR \$1;
tday VARCHAR(128);
tmonth VARCHAR(128);
tqtr VARCHAR(128);
tbegin TIMESTAMP WITH TIME ZONE;
tend TIMESTAMP WITH TIME ZONE;
n_ret INTEGER;
BEGIN
-- 2 quarters ago
tqtr := to_char(now() - interval ''6 months'', ''YYYY"q"Q'');
tbegin := date_trunc(''quarter'', now() - ''6 months''::interval);
tend := date_trunc(''quarter'', now() - ''3 months''::interval);
n_ret := tmp_mxl_threat_convert2(s_table, tqtr, tbegin, tend);
-- last quarter
tqtr := to_char(now() - interval ''3 months'', ''YYYY"q"Q'');
tbegin := tend;
tend := date_trunc(''quarter'', now());
n_ret := tmp_mxl_threat_convert2(s_table, tqtr, tbegin, tend);
.
.
More StoredProc / Function Code here
.
.
RETURN 0;
END;
' LANGUAGE 'plpgsql';
drop function tmp_mxl_threat_convert1 (VARCHAR);
END;
EOF
-Keaton
On 4/30/09 11:25 PM, "Carlo Stonebanks" <stonec.register@sympatico.ca> wrote:
One of our developers asked me, "is there any way to execute arbitrary
plpgsql"? By that I beleive he means: is there some way to execute ad-hoc
pl/pgsql code without creating a stored procedure or a function?
I believe MS SQL Server can do this - has any one heard of some sort of
command shell to do this for PG?
(I suppose one possibility would be something that created a temporary
stored proc to execute the code, then cleaned up after itself.)
Carlo
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general