Postgres function passing table reference and string in RETURN QUERY

Question

I'm trying to write a Postgres function for a pg_featureserv API (pg_featureserv function tutorial) but cannot figure out how to set it up. The function looks like this currently:

CREATE OR REPLACE FUNCTION postgisftw.webid( 
      webid_input text DEFAULT '8bacb710-4f3c-11eb-a8dd-48df37d0ac38_112000063',
      table_name text DEFAULT 'dz_earthquake_web_00078125_to_05',
      schema_name text DEFAULT 'emea_mea_naf'
)
RETURNS TABLE(webid text, geom geometry) 
AS $$ 
BEGIN 
    RETURN QUERY 
        EXECUTE
         format('SELECT i.webid::text,
               i.geom 
               FROM %I.%I as i
               WHERE i.webid = %I;', schema_name, table_name, web_input);
END; 
$$ 
LANGUAGE 'plpgsql' STABLE PARALLEL SAFE; 

SELECT postgisftw.webid();

The function should return a record with webid and geometry using the inputs: schema and table name as well as webid. However, I get an error:

ERROR:  column "web_input" does not exist
LINE 4: ...HERE i.webid ILIKE %I;', schema_name, table_name, web_input)
                                                             ^
QUERY:  SELECT format('SELECT i.webid::text,
               i.geom 
               FROM %I.%I as i
               WHERE i.webid = %I;', schema_name, table_name, web_input)
CONTEXT:  PL/pgSQL function postgisftw.webid(text,text,text) line 3 at RETURN QUERY
SQL state: 42703

I've tried with various quotation marks around web_input but am struggling. Can you please give me a hint?

Is it just that you meant to put webid_input rather than web_input? — Chris
– Chris, Commented Jan 5, 2021 at 21:30
Glad I could help a little bit, glad Adrian had the proper answer :-) — Chris
– Chris, Commented Jan 7, 2021 at 17:39

Adrian Klaver · Accepted Answer · 2021-01-05 21:39:42Z

2

%I is for an identifier which works for creating the schema.tablename portion of the query. This portion of the query, WHERE i.webid = %I is looking for a data value not an identifier. You need to use %L. In addition as @Chris pointed out you are using the wrong variable for that value.

answered Jan 5, 2021 at 21:39

Adrian Klaver

20.4k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres function passing table reference and string in RETURN QUERY

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related