CODEWITHSUNDEEP
postgresqlquotesplpgsql
user1945982
user1945982

Reputation: 63

plpgsql code that worked in postgresql 8.3 fails in 9.2

I have a problem with an array parameter going into a plpgsql function. My code works in PostgreSQL 8.3, but fails when called on a 9.2.1 server.

I have written a dummy function that shows the problem. This is based on some of the first plpgsql code I have written. I know it is ugly, so if it is not possible to escape my quotes in a way that works for both server versions, I am open to suggestions on rewriting this code so that it works on both server versions. Actually I am open to suggestions no matter what. I am not too good at plpgsql

So here's the dummy code that demonstrates the problem:

CREATE OR REPLACE FUNCTION get_test(collection text[])  
  RETURNS text AS  
$BODY$   
DECLARE   
counter int8; directive text; condition text; querytype text;   
BEGIN   
    counter = array_lower(collection, 1);   
    WHILE (counter <= array_upper(collection, 1)) LOOP   
        SELECT INTO directive  "?column?"[counter] FROM (SELECT collection) AS foo ;   
        counter = counter + 1;   
        SELECT INTO condition  "?column?"[counter] FROM (SELECT collection) AS foo ;   
        counter = counter + 1;   
        SELECT into querytype  "?column?"[counter] FROM (SELECT collection) AS foo ;   
        counter = counter + 1;   
    END LOOP;   
    RETURN 'dummy';   
END;   
$BODY$  
  LANGUAGE plpgsql VOLATILE  
  COST 100;  
ALTER FUNCTION get_test(text[]) OWNER TO postgres;

The collection parameter is built in sets of three (a simple string, a SQL condition, and another string). Here is an example call that works in 8.3 and fails in 9.2:

select * from get_test('{"dynamic","(tr.PROJECT_NAME = \'SampleProject\')","1"}')

This gives back 'dummy' as expected on 8.3, but fails on 9.2 with: ERROR: syntax error at or near "SampleProject" The caret points to the S of SampleProject, right after the single-quote, so I know I am not escaping the single quotes in the embedded SQL correctly.

I have tried this without the backslashes in front of the single quotes, with two backslashes, and finally with two single quotes, all to no avail. The original code is called by a Java client, but I have been testing this under pgadmin3 (version 1.16), connecting to two servers of the versions in question, trying different things.

Any ideas as to how I can make this call work?

Upvotes: 0

Views: 579

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658562

Most likely reason is a different setting for standard_conforming_strings.
Try this call:

The SQL-standard way (and recommended in PostgreSQL) to escape single quotes inside a single-quoted string literal is to double them:

SELECT *
FROM get_test('{dynamic,(tr.PROJECT_NAME = ''SampleProject''),1}'::text[])

OR:

SELECT *
FROM get_test('{dynamic,"(tr.PROJECT_NAME = ''SampleProject'')",1}'::text[])

Or you can resort to dollar-quoting to avoid multiple layers of escaping quotes

SELECT *
FROM get_test($${dynamic,"(tr.PROJECT_NAME = 'SampleProject')",1}$$::text[])

Or even an ARRAY constructor:

SELECT *
FROM get_test(ARRAY['dynamic','(tr.PROJECT_NAME = ''SampleProject'')','1'])

Upvotes: 1

Related Questions