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

You are using old-style function declaration where the function body is
given as a string enclosed in '. You have to escape all ' inside the
body by doubling them. As an alternative, you can use $$ as the begin
and end markers of your function body instead of the ' then you don't
need to escape.

Eus wrote:
> 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?
>
> Thank you.
>
> 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
>
>
>
>
>