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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
