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

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.

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