Skip site navigation (1) Skip section navigation (2)

Re: How to quote in plpgsql function for Execute dynamic queries

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: paallen(at)attglobal(dot)net
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to quote in plpgsql function for Execute dynamic queries
Date: 2001-08-18 14:51:07
Message-ID: 10960.998146267@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Phillip J. Allen" <paallen(at)attglobal(dot)net> writes:
>         dpsql := 'Select d.a_parm, d.f_parm, d.deltax, d.deltay FROM c_g_datum
> WHERE d.datum_id = ' || $1 || ';';      --this sql will only return 1 record

> So the real question is how do I formate the dpsql string.

You need to double or backslash-escape all those ' marks.  Remember that
what you are writing is itself one big string literal --- that's how
CREATE FUNCTION sees it, anyway.  The form depicted above is what you
want the value of the string literal to be, after the string-literal
parser gets done with it.  So, use '' or \' anywhere you need plpgsql
to see a '.

Somewhere there is a handy page that illustrates how many quote marks
to write in various complicated situations.  I thought it was at
http://techdocs.postgresql.org/ but didn't have much luck finding it.
Anyone remember what I'm thinking of?

			regards, tom lane

In response to

pgsql-novice by date

Next:From: booli2Date: 2001-08-19 07:48:50
Subject: sequence properties
Previous:From: Phillip J. AllenDate: 2001-08-18 12:10:22
Subject: How to quote in plpgsql function for Execute dynamic queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group