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 | VALUE
My 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=4326
I 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=4326
EDIT 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.
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.