Re: Why I cannot call a function from within an SQL function?

Hi Ho!

--- On Fri, 2/20/09, A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com> wrote:

> In response to Eus :
> > Hi Ho!
> >
> > The following query works well:
> >
> > select count (*)
> > from item_audit
> > where audit_ts >= '2008-05-30 00:00:00'
> > and audit_ts <= '2008-10-30
> 00:00:00'
> > and 'wst' != (select
> split_part(category, '-', 2)
> > from description
> > where split_part(category,
> '-', 1) = 'item'
> > and shorthand = status
> > )
> >
> > But, when I transform it into the following SQL
> function, the function cannot be created barking:
> >
> > ERROR: syntax error at or near "-"
> > LINE 6: and $1 != (select split_part(category,
> '-', 2)"
> >
> > create or replace function get_I(text, timestamp,
> timestamp) returns bigint as
> > 'select count (*)
> > from item_audit as ia
> > where audit_ts >= $2
> > and audit_ts <= $3
> > and $1 != (select split_part(category,
> '-', 2)
> > from description
> > where split_part(category,
> '-', 1) = 'item'
> > and shorthand = ia.status
> > )
> > ' language sql;
> >
> > What's wrong?
>
> The quoting. Use $$-quoting around the function, for
> instance:
>
> create or replace function get_I(text, timestamp,
> timestamp) returns bigint as $$
> select count (*) ...
>
> $$ language plpgsql;
>
> Now you can use simple ' inside the function. Other,
> but inferior solution,
> use ''' instead ' inside the function.

Wow, this is great!
Now I know the use of `$$'. Does the doc tell this? If yes, I really have missed it.

Thank you very much for telling me this.
You have saved me a lot of time from quoting a bunch of text.

> HTH, Andreas

Yes, it really helps. Thank you very much.

> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr:
> -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA
> http://wwwkeys.de.pgp.net

Best regards,
Eus (FSF member #4445)

In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices.

Join free software movement today! It is free as in freedom, not as in free beer!

Join: http://www.fsf.org/jf?referrer=4445