Thread: passing schema name and table name as parameter functions in postgresql 9.2
passing schema name and table name as parameter functions in postgresql 9.2
From
anushasrivastava03
Date:
I am trying to build a function which check for the schema and table in database if present then select the table values else create that schema and table and passing schema and table name as parameters of a function. CREATE OR REPLACE FUNCTION master.chkbypram(schemaname text,state text,wallmaterial text,roofmaterial text) RETURNS SETOF refcursor AS $BODY$ DECLARE Resulttable refcursor; tblinclusion text; Begin tblinclusion = 'inclusion'||'_'|| $2 ; if ( (execute 'SELECT exists(select schema_name FROM information_schema.schemata WHERE schema_name = '||$1||')')and ( execute 'select exists(select * from information_schema.tables where table_name='||tblinclusion||')') ) then OPEN Resulttable FOR execute ' select * from '||$1||'.'||$2 using schemaname,state; RETURN NEXT Resulttable; else execute 'CREATE SCHEMA '||&1 using schemaname; tblinclusion = 'inclusion'||'_'|| $2 ; execute 'create table '||&1||'.'||tblinclusion||' AS select * from master.population_2 where statecode = '||$2 || 'and distinct_key not in ( select distinct(distinct_key) from master.population_2 where ( statecode = '||$2 ||'and cast (substr(population_2.hhd_housingcodes, 4) as int ) >= 4 and substr(population_2.hhd_housingcodes, 1,1) in (SELECT code FROM regexp_split_to_table('||$3||', E',') AS code) and substr(population_2.hhd_housingcodes, 2,1) in (SELECT code FROM regexp_split_to_table('||$4||', E',') AS code) ) or statecode = '||$2 ||'and ( cast (substr(population_2.hhd_assetcodes, 4,1) as int)=3 or cast (substr(population_2.hhd_assetcodes, 5,1) as int)=1 or cast (substr(population_2.hhd_assetcodes, 3,1) as int)=1 ) or statecode = '||$2 ||'and ( (cast (substr(population_2.hhd_assetcodes, 1,1) as int)=1 and cast (substr(population_2.hhd_assetcodes, 2,1) as int)in(1,3) and cast (substr(population_2.hhd_assetcodes, 6,1) as int)=1) or (cast (substr(population_2.hhd_assetcodes, 1,1) as int)=1 and cast (substr(population_2.hhd_assetcodes, 2,1) as int)in(1,3) and cast (substr(population_2.hhd_assetcodes, 4,1) as int)=1) or (cast (substr(population_2.hhd_assetcodes, 1,1) as int)=1 and cast (substr(population_2.hhd_assetcodes, 6,1) as int)=1 and cast (substr(population_2.hhd_assetcodes, 4,1) as int)=1) or (cast (substr(population_2.hhd_assetcodes, 2,1) as int)in(1,3) and cast (substr(population_2.hhd_assetcodes, 6,1) as int)=1 and cast (substr(population_2.hhd_assetcodes, 4,1) as int)=1 ) ) ) ' using schemaname, state,wallmaterial,roofmaterial; OPEN Resulttable FOR execute ' select * from '||$1||'.'||tblinclusion using schemaname,state; RETURN NEXT Resulttable; end if; END; $BODY$ LANGUAGE plpgsql when calling this funtion it is throwing error select * from master.chkbypram('xyz','04','9,8','4,5,9'); ERROR: type "execute" does not exist LINE 1: SELECT ( (execute 'SELECT exists(select schema_name FROM inf... -- View this message in context: http://postgresql.1045698.n5.nabble.com/passing-schema-name-and-table-name-as-parameter-functions-in-postgresql-9-2-tp5758130.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: passing schema name and table name as parameter functions in postgresql 9.2
From
Pavel Stehule
Date:
Hello EXECUTE is not expression. You cannot use it inside IF statement. more, you don't need EXECUTE (dynamic DDL) in IF statement in your example. second issue is SQL injection vulnerability Regards Pavel Stehule 2013/6/6 anushasrivastava03 <anushasrivastava03@yahoo.co.in>: > I am trying to build a function which check for the schema and table in > database if present then select the table values else create that schema and > table and passing schema and table name as parameters of a function. > > > CREATE OR REPLACE FUNCTION master.chkbypram(schemaname text,state > text,wallmaterial text,roofmaterial text) > RETURNS SETOF refcursor AS > $BODY$ > DECLARE > Resulttable refcursor; > tblinclusion text; > Begin > tblinclusion = 'inclusion'||'_'|| $2 ; > if ( (execute 'SELECT exists(select schema_name FROM > information_schema.schemata WHERE schema_name = '||$1||')')and ( execute > 'select exists(select * from information_schema.tables where > table_name='||tblinclusion||')') ) > then > OPEN Resulttable FOR execute ' select * from '||$1||'.'||$2 using > schemaname,state; > RETURN NEXT Resulttable; > else > execute 'CREATE SCHEMA '||&1 using schemaname; > tblinclusion = 'inclusion'||'_'|| $2 ; > execute 'create table '||&1||'.'||tblinclusion||' AS > select * from master.population_2 where > statecode = '||$2 > || 'and distinct_key not in > ( > select distinct(distinct_key) > from master.population_2 > where > ( > statecode = '||$2 > ||'and cast (substr(population_2.hhd_housingcodes, 4) as int ) >= 4 > and substr(population_2.hhd_housingcodes, 1,1) in (SELECT code FROM > regexp_split_to_table('||$3||', E',') AS code) > and substr(population_2.hhd_housingcodes, 2,1) in (SELECT code FROM > regexp_split_to_table('||$4||', E',') AS code) > ) > or > statecode = '||$2 > ||'and > ( > cast (substr(population_2.hhd_assetcodes, 4,1) as int)=3 > or cast (substr(population_2.hhd_assetcodes, 5,1) as int)=1 > or cast (substr(population_2.hhd_assetcodes, 3,1) as int)=1 > ) > or > statecode = '||$2 > ||'and > ( (cast (substr(population_2.hhd_assetcodes, 1,1) as int)=1 and cast > (substr(population_2.hhd_assetcodes, 2,1) as int)in(1,3) and cast > (substr(population_2.hhd_assetcodes, 6,1) as int)=1) > or (cast (substr(population_2.hhd_assetcodes, 1,1) as int)=1 and cast > (substr(population_2.hhd_assetcodes, 2,1) as int)in(1,3) and cast > (substr(population_2.hhd_assetcodes, 4,1) as int)=1) > or (cast (substr(population_2.hhd_assetcodes, 1,1) as int)=1 and cast > (substr(population_2.hhd_assetcodes, 6,1) as int)=1 and cast > (substr(population_2.hhd_assetcodes, 4,1) as int)=1) > or (cast (substr(population_2.hhd_assetcodes, 2,1) as int)in(1,3) and > cast (substr(population_2.hhd_assetcodes, 6,1) as int)=1 and cast > (substr(population_2.hhd_assetcodes, 4,1) as int)=1 ) > ) > ) ' > using schemaname, state,wallmaterial,roofmaterial; > OPEN Resulttable FOR execute ' select * from '||$1||'.'||tblinclusion > using schemaname,state; > RETURN NEXT Resulttable; > end if; > END; > $BODY$ > LANGUAGE plpgsql > > when calling this funtion it is throwing error > select * from master.chkbypram('xyz','04','9,8','4,5,9'); > > > ERROR: type "execute" does not exist > LINE 1: SELECT ( (execute 'SELECT exists(select schema_name FROM inf... > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/passing-schema-name-and-table-name-as-parameter-functions-in-postgresql-9-2-tp5758130.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Re: passing schema name and table name as parameter functions in postgresql 9.2
From
anushasrivastava03
Date:
hi my requirement is to create a table as a result of sub-query and the table and schema name should be passed as parameters of a function i also tried like this; CREATE OR REPLACE FUNCTION secc_master.chkschema(schemaname text, tableis text) RETURNS void AS $BODY$ DECLARE ione boolean; BEGIN EXECUTE 'create table '||quote_literal($1)||'.'||quote_literal($2)||' as select * from master.master_state where state_code not in ( select distinct(statecode)from master.population_2 )'; end; $BODY$ LANGUAGE plpgsql select * from secc_master.chkschema('secc_master', 'chktbl') ERROR: syntax error at or near "'secc_master'" LINE 1: create table 'secc_master'.'chktbl' as select * from master... -- View this message in context: http://postgresql.1045698.n5.nabble.com/passing-schema-name-and-table-name-as-parameter-functions-in-postgresql-9-2-tp5758130p5758259.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: passing schema name and table name as parameter functions in postgresql 9.2
From
anushasrivastava03
Date:
i got it here i done it with 'quote_ident ' instead of 'quote_literal' -- View this message in context: http://postgresql.1045698.n5.nabble.com/passing-schema-name-and-table-name-as-parameter-functions-in-postgresql-9-2-tp5758130p5758260.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.