PL/pgSQL Syntax Error at end of Input

Question

I'm new to PL/pgSQL so this is kind of trial and error for me. I'm trying to create a blank function that when called will populate a table. Here is what I have so far:

create or replace function counties()
returns table(code varchar(16), county varchar(50), count int) as $$
DECLARE
    new_version varchar(50) := concat('sometext', max(REGEXP_REPLACE(COALESCE(schema_name, '0'), '[^0-9]*' ,'0')::integer), 'sometext') from information_schema.schemata where schema_name like 'gcversa%';
    old_version varchar(50) := concat('sometext', max(REGEXP_REPLACE(COALESCE(schema_name, '0'), '[^0-9]*' ,'0')::integer)-2, 'sometext') from information_schema.schemata where schema_name like 'gcversa%';
BEGIN RETURN QUERY
    with cte_current as (select distinct a.code as code, b.countyname as county from old_version a, public.counties b 
                 where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),

         cte_new     as (select distinct a.code as code, b.countyname as county from new_version a, public.counties b 
                 where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),


        cte_union as (select code, county from cte_current
        union all
        select code, county from cte_new)

    select code,county, count(*) as count
    from cte_union
    group by code, county
    Having count (*) <> 2;
END;
$$
LANGUAGE plpgsql;

The two variables that I have declared are referencing and concatenating two PostgreSQL schema's, and then I'm trying to insert those variables into the CTE's, in order to then union them all together. I got the function the execute properly, but when I call select counties(), I get this error:

ERROR:  relation "old_version" does not exist
LINE 1: ...tinct a.code as code, b.countyname as county from old_versio...

So, it's not picking up the variable that I declared, any suggestions?

UPDATE Per Vao Tsun's suggestion I utilized dynamic SQL and the format() function. Here is what the final working model looks like:

drop function counties();

create or replace function counties()
returns table(code varchar(16), county varchar(50), count bigint) as $$
DECLARE
    new_version_number varchar(50) := concat('gcversa00', MAX("versionnumber")) from "gcdefault"."versionhistory";
    old_version_number varchar(50) := concat('gcversa00', MAX("versionnumber"-2)) from "gcdefault"."versionhistory";
BEGIN RETURN QUERY EXECUTE
format(
    'with cte_current as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                 where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),

         cte_new     as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                 where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),


        cte_union as (select code, county from cte_current
        union all
        select code, county from cte_new)

    select code,county, count(*) as count
    from cte_union
    group by code, county
    Having count (*) <> 2', new_version_number, old_version_number
    );
END;
$$
LANGUAGE plpgsql;

you would need dynamic sql here - you can't use variable to put relation name in SQL — Vao Tsun
– Vao Tsun, Commented Nov 15, 2017 at 21:03

Vao Tsun · Accepted Answer · 2017-11-15 21:06:16Z

1

you would need dynamic sql here, eg:

t=# create or replace function ds() returns table (i int) as
$$
declare
tn text := 'pg_database';
begin
return query execute format('select oid::int from %I',tn);
end;
$$language plpgsql
;
CREATE FUNCTION
t=# select * from ds();
     i
-----------
         1
     12945
     12950
  12963038
 111274822
  32059515
  26947889
 173381559
  32061155
  32061156
  82287221
 203004236
 214018521
(13 rows)

answered Nov 15, 2017 at 21:06

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

4 Comments

Matt Over a year ago

I'm not sure I follow, can you clarify with an example pertinent to mine please?

Vao Tsun Over a year ago

basically you have to return query chnage to return query exexute and put query to th string

Matt Over a year ago

I'm going to update the case, the format tag is a lot like Python. The problem I'm having now is that because I'm referencing a table, it can't have quotes around it, and when I insert the table as a format object, it wants to add quotes to it. Thus postgre is saying the table doesn't exist...

Vao Tsun Over a year ago

not sure I follow - post a question? I read code much better - sorry :)

Collectives™ on Stack Overflow

PL/pgSQL Syntax Error at end of Input

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related