CODEWITHSUNDEEP
postgresqlpostgis
user2455068
user2455068

Reputation: 33

Postgres function passing table reference and string in RETURN QUERY

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?

Upvotes: 0

Views: 491

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19724

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

Upvotes: 2

Related Questions