Обсуждение: Why I cannot call a function from within an SQL function?

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
computingdevices. 

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

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

Hi Ho!

Sorry, let me revise the query a bit. I copied and pasted the original one from another big query.

--- On Fri, 2/20/09, Eus <eus@member.fsf.org> wrote:

> The following query works well:

select count (*)
from item_audit as ia
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 = ia.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
computingdevices. 

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

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

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

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
computingdevices. 
>
> Join free software movement today! It is free as in freedom, not as in free beer!
>
> Join: http://www.fsf.org/jf?referrer=4445
>
>
>
>
>

Hi Ho!

--- On Fri, 2/20/09, Miguel Ángel MF <michelangelo13@gmail.com> wrote:

> I'm no expert, but:
> i might say U should Escape the ` ' ´ char in
> "(select split_part(category,
> '-', 2) " using something like "(select
> split_part(category, \'-\', 2)" or
> however it should be...

Yes, you are right!
Thank you for telling me this.
I had been looking for this information for half an hour.

> A: Because it messes up the order in which people normally
> read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing in e-mail?
>
>
>
> http://www.brainyquote.com/quotes/authors/m/muhammad_ali.html
>
> http://www.brainyquote.com/quotes/authors/e/emma_goldman.html
>
> http://www.brainyquote.com/quotes/authors/m/michelangelo.html
>
>
>
>
> Bill Watterson  - "There is not enough time to do all
> the nothing we want to
> do."

Best regards,
Eus (FSF member #4445)

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

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

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

Use dollar quoting around your fiction body I'd double up on the
single quotes around the dash

  Sent from my iPhone

On Feb 20, 2009, at 8:14 AM, Eus <eus@member.fsf.org> wrote:

> Hi Ho!
>
> Sorry, let me revise the query a bit. I copied and pasted the
> original one from another big query.
>
> --- On Fri, 2/20/09, Eus <eus@member.fsf.org> wrote:
>
>> The following query works well:
>
> select count (*)
> from item_audit as ia
> 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 = ia.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
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Hi Ho!

--- On Fri, 2/20/09, A. Kretschmer <andreas.kretschmer@schollglas.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
computingdevices. 

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

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

Hi Ho!

--- On Fri, 2/20/09, Ketema Harris <ketema@ketema.net> wrote:

> Use dollar quoting around your fiction body I'd double
> up on the single quotes around the dash

Yup, I got it.
Thank you for your help.

>  Sent from my iPhone

Best regards,
Eus (FSF member #4445)

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

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

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

Hi Ho!

--- On Fri, 2/20/09, Marc Schablewski <ms@clickware.de> wrote:

> 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.

Ah, yes, after re-reading the doc, I found:

--- 8< ---
The syntax of the CREATE FUNCTION command requires the function body to be written as a string constant. It is usually
mostconvenient to use dollar quoting (see Section 4.1.2.2) for the string constant. If you choose to use regular
single-quotedstring constant syntax, you must double single quote marks (') and backslashes (\) (assuming escape string
syntax)in the body of the function (see Section 4.1.2.1).  
--- 8< ---

I really missed it. Sorry for making noise.

> 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.

Yes, this is much better.

Thank you very much for your explanation.

Best regards,
Eus (FSF member #4445)

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

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

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