Postgresql plpgsql function syntax error

Question

I want to create a function returning a set of records but I get a syntax error. The query is working but when I embed it in a function I get an error. What can be done to return the set?

Error
ERROR: syntax error at or near "select"
LINE 27: select id, user_id, project_id, record_date, parselid, pars...
________^
********** Error **********
ERROR: syntax error at or near "select"
SQL state: 42601
Character: 832

Function code

CREATE OR REPLACE FUNCTION getloggeom(
    IN p_lat double precision,
    IN p_lng double precision,
    OUT id integer, 
    OUT user_id bigint, 
    OUT project_id bigint, 
    OUT record_date timestamp without time zone, 
    OUT parselid integer, 
    OUT parselno character varying, 
    OUT adano character varying, 
    OUT ilid integer, 
    OUT ilad character varying, 
    OUT ilceid integer, 
    OUT ilcead character varying, 
    OUT mahallead character varying, 
    OUT mahalleid integer, 
    OUT pafta character varying, 
    OUT nitelik character varying, 
    OUT mevkii character varying, 
    OUT yuzolcum character varying, 
    OUT alan double precision, 
    OUT lat double precision, 
    OUT lng double precision, 
    OUT feature json)
  RETURNS SETOF record AS
$BODY$
    select id, user_id, project_id, record_date, parselid, parselno, adano, ilid, ilad, ilceid, ilcead, mahallead, mahalleid, pafta, nitelik, mevkii, yuzolcum, alan, st_x(st_centroid(st_transform(geom,4326))) as lng, st_y(st_centroid(st_transform(geom,4326))) as lat, (SELECT row_to_json(fc) as data
                     FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) as features
                     FROM ( SELECT 'Feature' as type
                        , ST_AsGeoJSON(st_transform(ST_MakeValid(geom),4326))::json as geometry
                        , row_to_json(( parselid, ilad, ilcead, mahallead, adano, parselno, yuzolcum, st_y(st_centroid(st_transform(geom,4326))), st_x(st_centroid(st_transform(geom,4326))))) As properties
                       FROM log_geom where st_intersects((st_transform(st_setsrid(st_point(p_lng, p_lat), 4326), 500000)),geom ) limit 1)as f )  as fc) as feature  from log_geom where         
        ST_Intersects(
        st_transform(st_setsrid(st_point(p_lng, p_lat), 4326), 500000),geom
    )limit 1;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000
  ROWS 1000;

JGH · Accepted Answer · 2017-07-07 02:17:49Z

0

If you use plpgsql, you must include the begin / end statements and you must use RETURN QUERY ... RETURN; as per the doc.

Otherwise you can switch to using SQL instead of PLPGSQL and your function would work as is.

CREATE OR REPLACE FUNCTION getloggeom([...])
  RETURNS SETOF record AS
$BODY$
    [...]
$BODY$
  LANGUAGE SQL
  COST 1000
  ROWS 1000;

answered Jul 7, 2017 at 2:17

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

Postgresql plpgsql function syntax error

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