Re: Help with quotes in plpgsql - Mailing list pgsql-sql

<div class="Section1"><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Hi Not sure about $1 parms : you may need to use a variable to set $1 to then cast it as interval :
</span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">create or replace function test(integer) returns setof text as $$</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">declare</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">   a record;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">begin</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">   select into a now() - <b><span
style="font-weight:bold">($1::text||'days')::interval</span></b>;</span></font><pclass="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">   return next a;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">   return;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">end</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">$$ language 'plpgsql';</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">-----Original Message-----<br /> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
OnBehalf Of Richard Ray<br /> Sent: Tuesday, December 19, 2006 12:10 PM<br /> To: pgsql-sql@postgresql.org<br />
Subject:[SQL] Help with quotes in plpgsql</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">How should this be properly quoted</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">create or replace function test(integer) returns setof text as $$</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">declare</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">   a record;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">begin</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">   select into a now() - interval '$1 day';</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">   return next a;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">   return;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">end</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">$$ language 'plpgsql';</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">I'm not having a lot of luck</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">Thanks</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">Richard</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">---------------------------(end of broadcast)---------------------------</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">TIP 7: You can help support the PostgreSQL project by donating at</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">                http://www.postgresql.org/about/donate</span></font></div>