Обсуждение: Stored Procedure table/column args
'psuedo
Can you create stored procedures that are built from parameters as
below, or does this defeat the pre-compiled purpose of an SP?
create function item_exists (tbl character varying, col character
varying, col_val character varying)
DECLARE
x integer;
PERFORM col FROM tbl
WHERE col="'" + col_val + "'"
IF FOUND THEN
x := 1;
else
x := 0;
end if;
RETURN x;
....
left out some syntax, but should deliver the idea
trying to build build a generic "check for existing", that's not
specific to a specific table/column without returning recordset
object overhead
any better ways
thanks
On 09/02/2013 06:06 PM, Bret Stern wrote: > 'psuedo > > Can you create stored procedures that are built from parameters as > below, or does this defeat the pre-compiled purpose of an SP? > > create function item_exists (tbl character varying, col character > varying, col_val character varying) > > DECLARE > x integer; > > PERFORM col FROM tbl > WHERE col="'" + col_val + "'" > IF FOUND THEN > x := 1; > else > x := 0; > end if; > > RETURN x; > > .... > > left out some syntax, but should deliver the idea > > trying to build build a generic "check for existing", that's not > specific to a specific table/column without returning recordset > object overhead > > any better ways http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > thanks > > > -- Adrian Klaver adrian.klaver@gmail.com
On Mon, 2013-09-02 at 18:20 -0700, Adrian Klaver wrote: > On 09/02/2013 06:06 PM, Bret Stern wrote: > > 'psuedo > > > > Can you create stored procedures that are built from parameters as > > below, or does this defeat the pre-compiled purpose of an SP? > > > > create function item_exists (tbl character varying, col character > > varying, col_val character varying) > > > > DECLARE > > x integer; > > > > PERFORM col FROM tbl > > WHERE col="'" + col_val + "'" > > IF FOUND THEN > > x := 1; > > else > > x := 0; > > end if; > > > > RETURN x; > > > > .... > > > > left out some syntax, but should deliver the idea > > > > trying to build build a generic "check for existing", that's not > > specific to a specific table/column without returning recordset > > object overhead > > > > any better ways > > http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN It's all there. Regards. B stern > > > thanks > > > > > > > >
Hello,
Create a view as described @
http://www.postgresql.org/message-id/flat/3C055B7E.BB52F0F1@but.auc.dk#3C055B7E.BB52F0F1@but.auc.dk
create view my_tbldescription as
select
u.usename, t.typname AS tblname,
a.attname, a.atttypid, n.typname AS atttypname,
int4larger(a.attlen, a.atttypmod - 4) AS atttyplen,
a.attnotnull, a.attnum
from pg_user u, pg_type t, pg_attribute a, pg_type n
where u.usesysid = t.typowner
and t.typrelid = a.attrelid and t.typtype = 'c' and not (t.typname ~* 'pg_')
and n.typelem = a.atttypid
and substr(n.typname, 1, 1) = '_'
and a.attnum > 0 ;
And then create functions using that view.
create or replace function table_exists (tbl varchar) returns boolean AS $$
DECLARE
x integer;
BEGIN
Execute 'select count(*) from my_tbldescription where tblname=$1' into x using tbl;
if (x>0)
then
RETURN TRUE;
else
RETURN FALSE;
end if;
END;
$$ LANGUAGE plpgsql;
create or replace function column_exists (col varchar) returns boolean AS $$
DECLARE
x integer;
BEGIN
Execute 'select count(*) from my_tbldescription where attname=$1' into x using col;
if (x>0)
then
RETURN TRUE;
else
RETURN FALSE;
end if;
END;
Regards
Sameer