CODEWITHSUNDEEP
postgresqlplpgsql
Matt
Matt

Reputation: 1119

PL/pgSQL Syntax Error at end of Input

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;

Upvotes: 1

Views: 5242

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51466

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)

Upvotes: 1

Related Questions