lundi 5 janvier 2015

Syntax Error In Postgresql Function


Why am I getting:



ERROR: syntax error at or near "'SELECT '"


In:



CREATE OR REPLACE FUNCTION GetListings (lon double precision, lat double precision, schemas name[])
RETURNS TABLE(
schema VARCHAR(128),
id BIGINT,
product_name VARCHAR(128),
product_type SMALLINT,
product_subtype SMALLINT,
product_units SMALLINT,
product_info JSONB,
product_image VARCHAR(256),
is_featured BOOLEAN,
is_special BOOLEAN,
is_alert BOOLEAN,
distributor_name VARCHAR(128),
distributor_image VARCHAR(256)
) AS $$
DECLARE
sch name;
n text;
t text;
BEGIN

n := to_char(lon,'999.999999999999999');
t := to_char(lat,'999.999999999999999');

FOREACH sch IN ARRAY schemas
LOOP
RETURN QUERY 'SELECT ''' || sch || ''', product_name, product_type, product_subtype, product_units, product_info, product_image, is_featured, is_special, is_alert, distributor_name, distributor_image FROM ' || quote_ident(sch) || '.listing WHERE ST_Contains(area, ST_SetSrid(ST_MakePoint(' || n || ', ' || t || '), 4326))';
END LOOP;

RETURN;

END;
$$ LANGUAGE plpgsql STRICT;




Aucun commentaire:

Enregistrer un commentaire