SQL state: 42601 when try to create view inside a function PostgreSQL

Question

I am getting sql state 42601 when executing following function. Can someone please help to resolve this error. I used this stackoverflow question to create the view from function. My function code is as below. I am trying to execute this function using select * from Test('DEPTA_');. I am getting error

SQL state: 42601
Context: PL/pgSQL function test(text) line 3 at EXECUTE statement

Function code :

create or replace function Test(authority text) returns void
as  $$
BEGIN
EXECUTE 
    'create materialized view '||authority ||' as
    WITH FINDUNDERSCORE as
    (select '||authority ||' as role, position(''_'' in '||authority||') as pos ),
    DISTINCT_ROLE as
    ( select  substring('||authority ||', 0, pos) as distinctRoles from FINDUNDERSCORE where position(''_'' in '||authority ||') > 1 and ''authority '' not like ''ROLE%''
        union select substring('||authority ||', pos+1, length('||authority ||')) as distinctRoles from FINDUNDERSCORE where position(''_'' in '||authority ||') > 1  and '||authority ||' not like ''ROLE%''
        union select '||authority ||' from FINDUNDERSCORE 
     ),

     ORIGINAL_ID as
     (select ROW_NUMBER() over(order by distinctRoles asc) as id, distinctRoles from DISTINCT_ROLE  order by distinctRoles asc),

    mapped_Id as
    ( select (case when oi.distinctroles ~ asid.sid then asid.id  end ) as newId, oi.id,oi.distinctroles,asid.sid, asid.id from original_id oi,acl_sid asid  ),

    AGGREGATE_NEWID as
    (select mi.newid,max(sid) sid, max(distinctroles) distinctroles, array_to_string(array_agg(mi.distinctroles),',') as aggregatedroles  from mapped_id mi where mi.newid is not null group by mi.newid ),

      MATCH_ACL_ENTRY as
      (select * from acl_entry ae join AGGREGATE_NEWID  asid on ae.sid = asid.newid and granting is true and  bitand(cast(ae.mask as bit(32)), cast(1 as bit(32)) ) = cast(1 as bit(32)) ) ,

       MATCH_ACL_OBJECT_IDENTITY as
      (select * from ACL_OBJECT_IDENTITY acl join MATCH_ACL_ENTRY asid on acl.id = asid.acl_object_identity),
        MATCH_ACL_PLATE as
        (select p.id, p.plate_barcode, p.plate_size, p.plate_id, acl.aggregatedroles, substring(acl.aggregatedroles,0,position(',' in acl.aggregatedroles)) as parentrole, 
        substring(acl.aggregatedroles,position(',' in acl.aggregatedroles)+1, length(acl.aggregatedroles)) as childrole from plate p join MATCH_ACL_OBJECT_IDENTITY acl on acl.object_id_identity = p.id)
        select id,plate_barcode,plate_size,plate_id from MATCH_ACL_PLATE';

END;
$$ LANGUAGE plpgsql;

alextunyk · Answer 1 · 2015-11-03T21:19:50.630

1

You've messed up at least three times while concatenating strings for EXECUTE statement. The SQL used to create view does not seem to be a valid one due to incorrect concatenation again.

My recommendation to you:

1st build a valid sql for view creation

2nd carefully replace required parts with variable concatenation

3rd you can always check log file to find out more information about errors you get

Good luck!

edited Nov 03 '15 at 21:19

answered Nov 02 '15 at 23:10

alextunyk

719
9
21

Thank you for your rply. Can you tell me what are the at least three instances where concatenation is wrong? My sql for creating view works when i replace authority variable to a string constant. – somename Nov 02 '15 at 23:26
Sure thing, please find combination of chars **','** single quotes actually closes the string, it must be escaped. – alextunyk Nov 02 '15 at 23:31
I figured it out i had to use three ' to get it considered as single quoted string and change duplicate column names. – somename Nov 03 '15 at 21:02

score 0 · Accepted Answer · answered Nov 03 '15 at 21:11

0

If anyone runs across same situation i solved this problem by adding three single quotes around parameter name which i want to consider as single quoted string

EXECUTE 
'create materialized view '||authority||' as
 WITH FINDUNDERSCORE as
 (select position(''_'' in '''||authority||''') as pos )
    ...

answered Nov 03 '15 at 21:11

somename

978
11
30

1

2 single quotes turns into one qoute (this is the way how to escape quotes) and 3rd one is for closing the string and concat with a variable so the result will be (select position(' _ ' in 'DEPTA_') as pos ) - then it looks like a good query (comparing to the original version it would generate (select position(' _ ' in DEPTA_) as pos ) and you get an error about DEPTA_) considering authority is set to 'DEPTA_' string – alextunyk Nov 03 '15 at 21:18

SQL state: 42601 when try to create view inside a function PostgreSQL

2 Answers2