User date_trunc function in plpgsql function

Started by annachauover 23 years ago2 messagesgeneral
Jump to latest
#1annachau
annachau@hongkong.com

How can I use datetrunc in plpgsql function with quote 'day'?

e.g.
str_QuerySql = ''SELECT 1 FROM ot_Source WHERE source_code = ''
|| quote_literal(str_Source ) ||
'' and status = ''
|| quote_literal(str_ACT) ||
'' and (current_date - date_trunc('day', effective_date) ) >= 0 '' ||;
open cursor_source FOR EXECUTE str_QuerySql;
FETCH cursor_source INTO int_RtnVal;

I got error on date_trunc('day', effective_date) because the 'day'. How can I quote
it? I have tried quote_literal() function like this:
str_day = ''day''
'' and (current_date - date_trunc('' || quote(str_day) || '', effective_date) ) >= 0 '' ||;
but show "attribute day not found"

Please help.

#2Scott Lamb
slamb@slamb.org
In reply to: annachau (#1)
Re: User date_trunc function in plpgsql function

annachau wrote:

How can I use datetrunc in plpgsql function with quote 'day'?

e.g.
str_QuerySql = ''SELECT 1 FROM ot_Source WHERE source_code = ''
|| quote_literal(str_Source ) ||
'' and status = ''
|| quote_literal(str_ACT) ||
'' and (current_date - date_trunc('day', effective_date) ) >= 0 '' ||;

You're quoting something inside a quoted string (str_QuerySql) inside
another quoted string (the PL/pgSQL function). You need to escape twice.
''''

Scott