Append Single quotes in Query Postgres Function?

Question

I am trying to append single quote in query in postgres function but result in error please have a look on my postgres function,

CREATE OR REPLACE function test() returns my_type as $$
    declare rd varchar := '56';
    declare personphone varchar := 'Philip Dannes';
    declare result my_type;
    declare SQL VARCHAR(300):=null; 
        BEGIN        
        -- Mandatory / Static part of the Query here
        SQL = 'select pt.id from product_template pt inner join product_product pp on pt.id=pp.id where  ';

        IF rd IS NOT NULL        
            then        
                 SQL =  SQL || 'pp.radio_phone = '|| rd;
        else   
   SQL =  SQL || 'pp.radio_phone = '|| rd;
        end if;       

        IF personphone IS NOT NULL        
            then      
                SQL = SQL || ' and pp.person_phone = '|| personphone;   
            else
  SQL = SQL || ' and pp.person_phone = '|| personphone;    
        end if;

        SQL = SQL || ';';    

        EXECUTE SQL;         
return result;        
    END
$$ LANGUAGE plpgsql;

when i execute it it gives me error on "Philip Daves" and return query after appending as,

Select pt.id from product_template pt inner join product_product pp on pt.id=pp.id where 
pp.radio_phone = 56 and  pp.person_phone = Philip Dave

I know error is because 56 and Philip Dave is not in single quote when i execute function return query with single quote it works fine.

How would i append Single quote in this query ??

I tried in this way like,

SQL = SQL || ' and pp.person_phone = '|| '' || personphone;

But i function return the same query

Hopes for your suggestion

Thanks in advance

Craig Ringer · Accepted Answer · 2014-05-27 06:09:42Z

4

You need to rewrite this rather heavily.

First, use quote_ident and quote_literal instead of manual quoting.

Better, use format() with the %I and %L specifiers for identifiers and literals if you're on a newer PostgreSQL version.

Also, try to avoid building strings iteratively like that. Build with an expression with CASEs where possible

There's no need for any varchar(300) business. Just use text.

Your ELSE clauses seem to contain the same thing as your IF ... THEN. I've removed them.

Try using RETURN QUERY EXECUTE or, if you're getting just one value, EXECUTE ... INTO.

DECLARE
    radiophone_clause text = '';
    personphone_clause text = '';
BEGIN        
    IF rd IS NOT NULL then
        radiophone_clause = 'and pp.radio_phone = '|| quote_literal(rd);
    END IF;

    IF personphone IS NOT NULL then      
        personphone_clause = ' and pp.person_phone = '|| quote_literal(personphone);
    END IF;

    RETURN QUERY EXECUTE format('select pt.id from product_template pt inner join product_product pp on pt.id=pp.id where true %s %s', radiophone_clause, personphone_clause);
END;

See the manual for info on quote_ident and quote_literal.

edited May 27, 2014 at 6:09

answered May 27, 2014 at 5:59

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

1 Comment

user3664724 Over a year ago

can i have any example for appending quotes as you mentioned ?

Collectives™ on Stack Overflow

Append Single quotes in Query Postgres Function?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related