用plpgsql存储过程返回一个表(Returning a table with plpgsql stored procedure)
我正在创建一个存储过程来复制在mapserver上的mapfile中找到的查询的输出。
查询：
select g.gid, g.geom, g.basin, a.\"DATE\", a.\"VALUE\" from sarffg_basins_00_regional_3sec as g join \"%prod_table%\" as a on g.basin = a.\"BASIN\" where a.\"DATE\" = '%prod_date%'由于SO帖子长度限制，我不能完整地发布输出，但重要的是这个查询给了我输出列名。 调整语法以匹配在sql shell中运行的传统sql：
select g.gid, g.geom, g.basin, a."DATE", a."VALUE" from sarffg_basins_00_regional_3sec as g join "FFGS_PROCESSED_PRODUCT_ASM_SACSMA_UPPER_BASIN_TIMESERIES" as a on g.basin = a."BASIN" where a."DATE" = '2017-01-12 18:00:00+00';给出了列
gid | geom | basin | DATE | VALUE我的存储过程具有以下返回标头：
RETURNS table ( gid integer, geom geometry(MultiPolygon,4326), basin double precision, date timestamptz, value double precision )并且返回声明：
RETURN QUERY EXECUTE 'SELECT g.'||quote_ident('gid')||', g.'||quote_ident('geom')||', g.'||quote_ident('basin')||', a.'||quote_ident('DATE')||', a.'||quote_ident('VALUE')||' FROM sarffg_basins_00_regional_3sec AS g JOIN '||quote_ident(prod_table)||' AS a ON g.'||quote_ident('basin')||' = a.'||quote_ident('BASIN')||' WHERE a.'||quote_ident('DATE')||' = '''||adj_timestamp||'''';这给了我相同的输出，但没有列名。 我认为这是我的mapfile错误日志中出现此错误的原因：
Query error. Error executing query: ERROR: column "VALUE" does not exist LINE 1: select "VALUE",encode(ST_AsBinary(ST_Force2D("geom"),'NDR'),...
我知道prod_table中存在“VALUE”列，特别是考虑到这个函数在psql shell中调用时工作正常。
我需要做什么才能使输出具有列标题？
编辑：mapfile上的更多上下文。
mapfile中的整个查询如下：
geom from (select g.gid, g.geom, g.basin, a.\"DATE\", a.\"VALUE\" from sarffg_basins_00_regional_3sec as g join \"%prod_table%\" as a on g.basin = a.\"BASIN\" where a.\"DATE\" = '%prod_date%') as subquery using unique gid using srid=4326我基本上需要使用存储过程复制此功能，无论是完全还是仅仅是子查询。 我可以在此处找到各种尝试： “USING”或其附近的语法错误 。 似乎我最接近试图只复制子查询 - 即
geom from (select * from ingest_ffgs_prod_composite_csv('%prod_table%', 1484438400)) as subquery using unique gid using srid=4326编辑2：既然我花了更多的时间来思考它，我不确定当查询只从我的存储过程返回的子查询中选择“geom”时，为什么“VALUE”将是缺少的列。 也许这不是导致错误的原因。
编辑3：根据要求，这是整个功能。
CREATE OR REPLACE FUNCTION ingest_ffgs_prod_composite_csv(prod_table text, epoch_seconds bigint, original boolean DEFAULT false, roll_back boolean DEFAULT true) RETURNS table ( gid integer, geom geometry(MultiPolygon,4326), basin double precision, date timestamptz, VALUE double precision ) AS $$ DECLARE c01n text := 'BASIN'; c01t text := 'double precision'; c02n text := 'MAP01'; c02t text := 'double precision'; c03n text := 'MAP03'; c03t text := 'double precision'; c04n text := 'MAP06'; c04t text := 'double precision'; c05n text := 'MAP24'; c05t text := 'double precision'; c06n text := 'GMAP06'; c06t text := 'double precision'; c07n text := 'GMAP24'; c07t text := 'double precision'; c08n text := 'ASMU06'; c08t text := 'double precision'; c09n text := 'ASML06'; c09t text := 'double precision'; c10n text := 'ASMT06'; c10t text := 'double precision'; c11n text := 'FFG01'; c11t text := 'double precision'; c12n text := 'FFG03'; c12t text := 'double precision'; c13n text := 'FFG06'; c13t text := 'double precision'; c14n text := 'PREVFFG01'; c14t text := 'double precision'; c15n text := 'PREVFFG03'; c15t text := 'double precision'; c16n text := 'PREVFFG06'; c16t text := 'double precision'; c17n text := 'FMAP01'; c17t text := 'double precision'; c18n text := 'FMAP03'; c18t text := 'double precision'; c19n text := 'FMAP06'; c19t text := 'double precision'; c20n text := 'IFFT01'; c20t text := 'double precision'; c21n text := 'IFFT03'; c21t text := 'double precision'; c22n text := 'IFFT06'; c22t text := 'double precision'; c23n text := 'PFFT01'; c23t text := 'double precision'; c24n text := 'PFFT03'; c24t text := 'double precision'; c25n text := 'PFFT06'; c25t text := 'double precision'; c26n text := 'FFFT01'; c26t text := 'double precision'; c27n text := 'FFFT03'; c27t text := 'double precision'; c28n text := 'FFFT06'; c28t text := 'double precision'; c29n text := 'PET06'; c29t text := 'double precision'; tablename_csv text := 'temp_table_csv'; tablename_ts text := 'temp_table_ts'; tablename_ret text := 'temp_table_ret'; full_timestamp timestamptz; adj_timestamp_text text; adj_timestamp timestamptz; year double precision; month double precision; day double precision; hour double precision; year_text text; month_text text; day_text text; hour_text text; csv_base_path text; csv_filename text; csv_file_full_path text; ret_rec record; product text; interval text; curr_basin int; curr_val double precision; rec record; existing_table boolean; existing_data boolean := false; ret_geom geometry(MultiPolygon,4326); BEGIN -- parse prod_table name to determine which product and time interval we need to build a timeseries for ------------------------------------------------------------------------------------------------------- product := split_part(prod_table, '_', 4); interval := rtrim(split_part(prod_table, '_', 6), 'HR'); IF interval = 'UPPE' THEN -- asm interval reads as 'UPPE'. Default to 6 hours interval := '06'; END IF; raise notice 'product: %', product; raise notice 'interval: %', interval; -- convert epoch seconds to timestamp for parsing ------------------------------------------------- full_timestamp := to_timestamp(epoch_seconds); year := date_part('year', full_timestamp); month := date_part('month', full_timestamp); day := date_part('day', full_timestamp); IF roll_back THEN hour := date_part('hour', full_timestamp) - (date_part('hour', full_timestamp)::integer % interval::int); ELSE hour := date_part('hour', full_timestamp); END IF; year_text := year; month_text := month; day_text := day; hour_text := hour; IF month < 10 THEN month_text := '0' || month; END IF; IF day < 10 THEN day_text := '0' || day; END IF; IF hour < 10 THEN hour_text := '0' || hour; END IF; adj_timestamp_text := year_text || '-' || month_text || '-' || day_text || ' ' || hour_text || ':00:00+00'; adj_timestamp := adj_timestamp_text::timestamptz; raise notice 'year: %', year_text; raise notice 'month: %', month_text; raise notice 'day: %', day_text; raise notice 'hour: %', hour_text; -- check if table with the desired data already exists within the database -------------------------------------------------------------------------- EXECUTE format('SELECT EXISTS ( SELECT 1 FROM pg_tables WHERE schemaname = ''public'' AND tablename = '''||prod_table||''')') INTO existing_table; IF existing_table THEN EXECUTE format('SELECT EXISTS ( SELECT 1 FROM '||quote_ident(prod_table)||' WHERE '||quote_ident('DATE')||' = '''||adj_timestamp||''')') INTO existing_data; END IF; raise notice 'existing_table: %', existing_table; raise notice 'existing_data: %', existing_data; IF NOT existing_data THEN -- need to manually create tables from csv file -- construct full path to csv file ---------------------------------- csv_base_path := '/SARFFG/EXP/DATA/EXPORTS/REGIONAL/'||year_text||'/'||month_text||'/'||day_text||'/COMPOSITE_CSV'; IF original THEN csv_filename := year_text||month_text||day_text||'-'||hour_text||'00_ffgs_prod_composite_table_01hr_regional_original.csv'; ELSE csv_filename := year_text||month_text||day_text||'-'||hour_text||'00_ffgs_prod_composite_table_01hr_regional.csv'; END IF; csv_file_full_path := csv_base_path||'/'||csv_filename; ----------------------------------------------- raise notice 'csv file: %', csv_file_full_path; -- create temporary table to store CSV contents ----------------------------------------------- EXECUTE format('CREATE TEMP TABLE '||tablename_csv||' ('||c01n||' '||c01t||', '||c02n||' '||c02t||', '||c03n||' '||c03t||', '||c04n||' '||c04t||', '||c05n||' '||c05t||', '||c06n||' '||c06t||', '||c07n||' '||c07t||', '||c08n||' '||c08t||', '||c09n||' '||c09t||', '||c10n||' '||c10t||', '||c11n||' '||c11t||', '||c12n||' '||c12t||', '||c13n||' '||c13t||', '||c14n||' '||c14t||', '||c15n||' '||c15t||', '||c16n||' '||c16t||', '||c17n||' '||c17t||', '||c18n||' '||c18t||', '||c19n||' '||c19t||', '||c20n||' '||c20t||', '||c21n||' '||c21t||', '||c22n||' '||c22t||', '||c23n||' '||c23t||', '||c24n||' '||c24t||', '||c25n||' '||c25t||', '||c26n||' '||c26t||', '||c27n||' '||c27t||', '||c28n||' '||c28t||', '||c29n||' '||c29t||') ON COMMIT DROP'); EXECUTE format('COPY '||tablename_csv||' FROM '''||csv_file_full_path||''' DELIMITER '','' CSV HEADER'); -- create temp timeseries table ------------------------------- EXECUTE format('CREATE TEMP TABLE '||tablename_ts||' (date timestamptz, basin int, value double precision) ON COMMIT DROP'); FOR rec in SELECT * FROM temp_table_csv LOOP curr_basin := rec.basin; IF product = 'MAP' THEN IF interval = '01' THEN curr_val := rec.map01; ELSIF interval = '03' THEN curr_val := rec.map03; ELSIF interval = '06' THEN curr_val := rec.map06; ELSIF interval = '24' THEN curr_val := rec.map24; ELSE raise warning 'interval not found'; EXIT; END IF; ELSIF product = 'ASM' THEN IF interval = '06' THEN curr_val := rec.asmu06; ELSE raise warning 'interval not found'; EXIT; END IF; ELSIF product = 'FFG' THEN IF interval = '01' THEN curr_val := rec.ffg01; ELSIF interval = '03' THEN curr_val := rec.ffg03; ELSIF interval = '06' THEN curr_val := rec.ffg06; ELSE raise warning 'interval not found'; EXIT; END IF; ELSIF product = 'FFT' THEN IF interval = '01' THEN curr_val := rec.fft01; ELSIF interval = '03' THEN curr_val := rec.fft03; ELSIF interval = '06' THEN curr_val := rec.fft06; ELSE raise warning 'interval not found'; EXIT; END IF; ELSE raise warning 'product not found'; EXIT; END IF; INSERT INTO temp_table_ts (date, basin, value) VALUES (adj_timestamp, curr_basin, curr_val); END LOOP; RETURN QUERY EXECUTE 'SELECT g.gid, g.geom, g.basin, a.date, a.value FROM sarffg_basins_00_regional_3sec AS g JOIN '||tablename_ts||' AS a ON g.'||quote_ident('basin')||' = a.'||quote_ident('basin')||' WHERE a.'||quote_ident('date')||' = '''||adj_timestamp||''''; ELSE RETURN QUERY EXECUTE format('SELECT g.gid, g.geom, g.basin, a."DATE", a."VALUE" FROM sarffg_basins_00_regional_3sec AS g JOIN %I AS a ON g.basin = a."BASIN" WHERE a."DATE" = $1', prod_table) using adj_timestamp; END IF; END; $$ LANGUAGE plpgsql;编辑4：经过一些更多的尽职调查，我回想起问题来自我的输出中缺少列名。 我的颜色标记明确引用了“VALUE”，但显然没有列名，它不知道哪个是“VALUE”。
将我的查询更改为
select gid, geom, basin, "DATE", "VALUE" from ingest_ffgs_prod_composite_csv('%prod_table%', 1484438400)给我我的专栏名称，但不幸的是没有解决我的问题。
I am creating a stored procedure to replicate the output of a query found within a mapfile on our mapserver.
The query:
select g.gid, g.geom, g.basin, a.\"DATE\", a.\"VALUE\" from sarffg_basins_00_regional_3sec as g join \"%prod_table%\" as a on g.basin = a.\"BASIN\" where a.\"DATE\" = '%prod_date%'I cannot post the output in whole because of SO post length limits, but the important thing is that this query gives me my output with column names. Adjusting the syntax to match conventional sql to be run inside of an sql shell:
select g.gid, g.geom, g.basin, a."DATE", a."VALUE" from sarffg_basins_00_regional_3sec as g join "FFGS_PROCESSED_PRODUCT_ASM_SACSMA_UPPER_BASIN_TIMESERIES" as a on g.basin = a."BASIN" where a."DATE" = '2017-01-12 18:00:00+00';gives the columns
gid | geom | basin | DATE | VALUEMy stored procedure has the following return header:
RETURNS table ( gid integer, geom geometry(MultiPolygon,4326), basin double precision, date timestamptz, value double precision )And the return statement:
RETURN QUERY EXECUTE 'SELECT g.'||quote_ident('gid')||', g.'||quote_ident('geom')||', g.'||quote_ident('basin')||', a.'||quote_ident('DATE')||', a.'||quote_ident('VALUE')||' FROM sarffg_basins_00_regional_3sec AS g JOIN '||quote_ident(prod_table)||' AS a ON g.'||quote_ident('basin')||' = a.'||quote_ident('BASIN')||' WHERE a.'||quote_ident('DATE')||' = '''||adj_timestamp||'''';This gives me the same output, with the exception of no column names. I think this is the cause of this error in my mapfile error log:
Query error. Error executing query: ERROR: column "VALUE" does not exist LINE 1: select "VALUE",encode(ST_AsBinary(ST_Force2D("geom"),'NDR'),...
I know the column "VALUE" exists within prod_table, especially considering this function works fine when called within a psql shell.
What do I need to do to make my output have the column headers?
EDIT: A little more context on the mapfile.
The entire query within the mapfile is as follows:
geom from (select g.gid, g.geom, g.basin, a.\"DATE\", a.\"VALUE\" from sarffg_basins_00_regional_3sec as g join \"%prod_table%\" as a on g.basin = a.\"BASIN\" where a.\"DATE\" = '%prod_date%') as subquery using unique gid using srid=4326I basically need to replicate this functionality with a stored procedure, either in its entirely or just the subquery. My various attempts can be found here: Syntax error at or near "USING" . It seems I have come the closest with trying to replicate only the subquery - i.e.
geom from (select * from ingest_ffgs_prod_composite_csv('%prod_table%', 1484438400)) as subquery using unique gid using srid=4326EDIT 2: Now that I have spent more time thinking about it, I'm not sure why "VALUE" would be the missing column when the query is only selecting "geom" from the subquery returned from my stored procedure. Maybe this is not the cause of the error after all.
EDIT 3: As requested, here is the entire function.
CREATE OR REPLACE FUNCTION ingest_ffgs_prod_composite_csv(prod_table text, epoch_seconds bigint, original boolean DEFAULT false, roll_back boolean DEFAULT true) RETURNS table ( gid integer, geom geometry(MultiPolygon,4326), basin double precision, date timestamptz, VALUE double precision ) AS $$ DECLARE c01n text := 'BASIN'; c01t text := 'double precision'; c02n text := 'MAP01'; c02t text := 'double precision'; c03n text := 'MAP03'; c03t text := 'double precision'; c04n text := 'MAP06'; c04t text := 'double precision'; c05n text := 'MAP24'; c05t text := 'double precision'; c06n text := 'GMAP06'; c06t text := 'double precision'; c07n text := 'GMAP24'; c07t text := 'double precision'; c08n text := 'ASMU06'; c08t text := 'double precision'; c09n text := 'ASML06'; c09t text := 'double precision'; c10n text := 'ASMT06'; c10t text := 'double precision'; c11n text := 'FFG01'; c11t text := 'double precision'; c12n text := 'FFG03'; c12t text := 'double precision'; c13n text := 'FFG06'; c13t text := 'double precision'; c14n text := 'PREVFFG01'; c14t text := 'double precision'; c15n text := 'PREVFFG03'; c15t text := 'double precision'; c16n text := 'PREVFFG06'; c16t text := 'double precision'; c17n text := 'FMAP01'; c17t text := 'double precision'; c18n text := 'FMAP03'; c18t text := 'double precision'; c19n text := 'FMAP06'; c19t text := 'double precision'; c20n text := 'IFFT01'; c20t text := 'double precision'; c21n text := 'IFFT03'; c21t text := 'double precision'; c22n text := 'IFFT06'; c22t text := 'double precision'; c23n text := 'PFFT01'; c23t text := 'double precision'; c24n text := 'PFFT03'; c24t text := 'double precision'; c25n text := 'PFFT06'; c25t text := 'double precision'; c26n text := 'FFFT01'; c26t text := 'double precision'; c27n text := 'FFFT03'; c27t text := 'double precision'; c28n text := 'FFFT06'; c28t text := 'double precision'; c29n text := 'PET06'; c29t text := 'double precision'; tablename_csv text := 'temp_table_csv'; tablename_ts text := 'temp_table_ts'; tablename_ret text := 'temp_table_ret'; full_timestamp timestamptz; adj_timestamp_text text; adj_timestamp timestamptz; year double precision; month double precision; day double precision; hour double precision; year_text text; month_text text; day_text text; hour_text text; csv_base_path text; csv_filename text; csv_file_full_path text; ret_rec record; product text; interval text; curr_basin int; curr_val double precision; rec record; existing_table boolean; existing_data boolean := false; ret_geom geometry(MultiPolygon,4326); BEGIN -- parse prod_table name to determine which product and time interval we need to build a timeseries for ------------------------------------------------------------------------------------------------------- product := split_part(prod_table, '_', 4); interval := rtrim(split_part(prod_table, '_', 6), 'HR'); IF interval = 'UPPE' THEN -- asm interval reads as 'UPPE'. Default to 6 hours interval := '06'; END IF; raise notice 'product: %', product; raise notice 'interval: %', interval; -- convert epoch seconds to timestamp for parsing ------------------------------------------------- full_timestamp := to_timestamp(epoch_seconds); year := date_part('year', full_timestamp); month := date_part('month', full_timestamp); day := date_part('day', full_timestamp); IF roll_back THEN hour := date_part('hour', full_timestamp) - (date_part('hour', full_timestamp)::integer % interval::int); ELSE hour := date_part('hour', full_timestamp); END IF; year_text := year; month_text := month; day_text := day; hour_text := hour; IF month < 10 THEN month_text := '0' || month; END IF; IF day < 10 THEN day_text := '0' || day; END IF; IF hour < 10 THEN hour_text := '0' || hour; END IF; adj_timestamp_text := year_text || '-' || month_text || '-' || day_text || ' ' || hour_text || ':00:00+00'; adj_timestamp := adj_timestamp_text::timestamptz; raise notice 'year: %', year_text; raise notice 'month: %', month_text; raise notice 'day: %', day_text; raise notice 'hour: %', hour_text; -- check if table with the desired data already exists within the database -------------------------------------------------------------------------- EXECUTE format('SELECT EXISTS ( SELECT 1 FROM pg_tables WHERE schemaname = ''public'' AND tablename = '''||prod_table||''')') INTO existing_table; IF existing_table THEN EXECUTE format('SELECT EXISTS ( SELECT 1 FROM '||quote_ident(prod_table)||' WHERE '||quote_ident('DATE')||' = '''||adj_timestamp||''')') INTO existing_data; END IF; raise notice 'existing_table: %', existing_table; raise notice 'existing_data: %', existing_data; IF NOT existing_data THEN -- need to manually create tables from csv file -- construct full path to csv file ---------------------------------- csv_base_path := '/SARFFG/EXP/DATA/EXPORTS/REGIONAL/'||year_text||'/'||month_text||'/'||day_text||'/COMPOSITE_CSV'; IF original THEN csv_filename := year_text||month_text||day_text||'-'||hour_text||'00_ffgs_prod_composite_table_01hr_regional_original.csv'; ELSE csv_filename := year_text||month_text||day_text||'-'||hour_text||'00_ffgs_prod_composite_table_01hr_regional.csv'; END IF; csv_file_full_path := csv_base_path||'/'||csv_filename; ----------------------------------------------- raise notice 'csv file: %', csv_file_full_path; -- create temporary table to store CSV contents ----------------------------------------------- EXECUTE format('CREATE TEMP TABLE '||tablename_csv||' ('||c01n||' '||c01t||', '||c02n||' '||c02t||', '||c03n||' '||c03t||', '||c04n||' '||c04t||', '||c05n||' '||c05t||', '||c06n||' '||c06t||', '||c07n||' '||c07t||', '||c08n||' '||c08t||', '||c09n||' '||c09t||', '||c10n||' '||c10t||', '||c11n||' '||c11t||', '||c12n||' '||c12t||', '||c13n||' '||c13t||', '||c14n||' '||c14t||', '||c15n||' '||c15t||', '||c16n||' '||c16t||', '||c17n||' '||c17t||', '||c18n||' '||c18t||', '||c19n||' '||c19t||', '||c20n||' '||c20t||', '||c21n||' '||c21t||', '||c22n||' '||c22t||', '||c23n||' '||c23t||', '||c24n||' '||c24t||', '||c25n||' '||c25t||', '||c26n||' '||c26t||', '||c27n||' '||c27t||', '||c28n||' '||c28t||', '||c29n||' '||c29t||') ON COMMIT DROP'); EXECUTE format('COPY '||tablename_csv||' FROM '''||csv_file_full_path||''' DELIMITER '','' CSV HEADER'); -- create temp timeseries table ------------------------------- EXECUTE format('CREATE TEMP TABLE '||tablename_ts||' (date timestamptz, basin int, value double precision) ON COMMIT DROP'); FOR rec in SELECT * FROM temp_table_csv LOOP curr_basin := rec.basin; IF product = 'MAP' THEN IF interval = '01' THEN curr_val := rec.map01; ELSIF interval = '03' THEN curr_val := rec.map03; ELSIF interval = '06' THEN curr_val := rec.map06; ELSIF interval = '24' THEN curr_val := rec.map24; ELSE raise warning 'interval not found'; EXIT; END IF; ELSIF product = 'ASM' THEN IF interval = '06' THEN curr_val := rec.asmu06; ELSE raise warning 'interval not found'; EXIT; END IF; ELSIF product = 'FFG' THEN IF interval = '01' THEN curr_val := rec.ffg01; ELSIF interval = '03' THEN curr_val := rec.ffg03; ELSIF interval = '06' THEN curr_val := rec.ffg06; ELSE raise warning 'interval not found'; EXIT; END IF; ELSIF product = 'FFT' THEN IF interval = '01' THEN curr_val := rec.fft01; ELSIF interval = '03' THEN curr_val := rec.fft03; ELSIF interval = '06' THEN curr_val := rec.fft06; ELSE raise warning 'interval not found'; EXIT; END IF; ELSE raise warning 'product not found'; EXIT; END IF; INSERT INTO temp_table_ts (date, basin, value) VALUES (adj_timestamp, curr_basin, curr_val); END LOOP; RETURN QUERY EXECUTE 'SELECT g.gid, g.geom, g.basin, a.date, a.value FROM sarffg_basins_00_regional_3sec AS g JOIN '||tablename_ts||' AS a ON g.'||quote_ident('basin')||' = a.'||quote_ident('basin')||' WHERE a.'||quote_ident('date')||' = '''||adj_timestamp||''''; ELSE RETURN QUERY EXECUTE format('SELECT g.gid, g.geom, g.basin, a."DATE", a."VALUE" FROM sarffg_basins_00_regional_3sec AS g JOIN %I AS a ON g.basin = a."BASIN" WHERE a."DATE" = $1', prod_table) using adj_timestamp; END IF; END; $$ LANGUAGE plpgsql;EDIT 4: After some more due diligence, I am back to thinking the issue comes from a lack of column names in my output. My colorscales reference "VALUE" explicitly, but obviously without column names it doesn't know which one is "VALUE".
Changing my query to
select gid, geom, basin, "DATE", "VALUE" from ingest_ffgs_prod_composite_csv('%prod_table%', 1484438400)Gives me my column names, but doesn't solve my issue unfortunately.
最满意答案
你的问题太混乱了。 不过，我确实注意到引用标识符的问题。 函数在返回类型中声明：
... RETURNS table ( ... date timestamptz, VALUE double precision ) ...但函数调用不匹配：
select gid, geom, basin, "DATE", "VALUE" from ingest_ffgs_prod_composite_csv(...这完全解释了您的错误消息：
ERROR: column "VALUE" does not exist
因为没有列"VALUE" 。 只有value （双引或不引用）。
所有未加引号的标识符都在Postgres中转换为小写。 因此， VALUE和value （但不是 "VALUE"或"Value" ）与标识符相同。 但不是字符串。 当使用'%I'传递给quote_ident()或format()时，情况很重要，因为它是转义的。 所以：
a.'||quote_ident('DATE')||', a.'||quote_ident('VALUE')||'产生a."DATE", a."VALUE" ，它与a.DATE, a.VALUE 。
此外，PL / pgSQL或SQL函数体中的列名在外部不可见。 只有声明的返回类型才对函数调用很重要。
我通常建议不要将date或value作为列名。 Postgres中允许使用这两种语言，但标准SQL中使用保留字。
仅使用合法的，小写的，不带引号的标识符来避免这种混淆。
PostgreSQL列名是否区分大小写？Your question is too confusing. I did notice issues with quoted identifiers, though. The function declares in the return type:
... RETURNS table ( ... date timestamptz, VALUE double precision ) ...But the function call does not match:
select gid, geom, basin, "DATE", "VALUE" from ingest_ffgs_prod_composite_csv(...This explains your error message perfectly:
ERROR: column "VALUE" does not exist
Because there is no column "VALUE". Only value (double-quoted or not).
All unquoted identifiers are cast to lower case in Postgres. So VALUE and value (but not "VALUE" or "Value") are identical as identifiers. But not as strings. When passed to quote_ident() or format() with '%I', case is significant, since it is escaped. So:
a.'||quote_ident('DATE')||', a.'||quote_ident('VALUE')||'produces a."DATE", a."VALUE", which is distinct from a.DATE, a.VALUE.
Also, column names in the body of a PL/pgSQL or SQL function are not visible outside. Only the declared return type matters for the function call.
I would generally advise against date or value as column names. Both are allowed in Postgres, but reserved words in standard SQL.
Use legal, lower-case, unquoted identifiers exclusively to avoid such confusion.
Are PostgreSQL column names case-sensitive?更多推荐
发布评论