Stored Procedure table/column args

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,

Create a view as described @

/messages/by-id/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