Re: Any way to execute ad-hoc pl/pgsql?

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(dot)register(at)sympatico(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general