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

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

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

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

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

On Fri, Feb 20, 2009 at 2:02 PM, Eus <eus@member.fsf.org> wrote:

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:

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!

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

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

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

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 most convenient to use dollar quoting (see Section 4.1.2.2) for the string constant. If you choose to use regular single-quoted string 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 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