在postgresql 9.2中传递模式名称和表名作为参数函数 [英] passing schema name and table name as parameter functions in postgresql 9.2

问题描述

我正在尝试构建一个函数来检查数据库中的模式和表（如果存在），然后选择表值，否则创建该模式和表，并将模式和表名作为函数的参数传递。

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

在调用此函数时，它会抛出错误

select * from master.chkbypram（''xyz''，''04''，''9,8''，''4,5,9 ''）;

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...

推荐答案

BODY

DECLARE
Resulttable refcursor;
tblinclusion text ;
开始
tblinclusion = ' 包含' || ' _' ||

DECLARE Resulttable refcursor; tblinclusion text; Begin tblinclusion = 'inclusion'||'_'||

2 ;
if （（执行 ' SELECT存在（选择schema_name FROM information_schema.schemata WHERE schema_name =' ||

2 ; if ( (execute 'SELECT exists(select schema_name FROM information_schema.schemata WHERE schema_name = '||

这篇关于在postgresql 9.2中传递模式名称和表名作为参数函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！