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

2009-02-20 Thread Eus 
Hi Ho!

--- On Fri, 2/20/09, Marc Schablewski  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


  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-02-20 Thread Eus 
Hi Ho!

--- On Fri, 2/20/09, Ketema Harris  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


  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-02-20 Thread Eus 
Hi Ho!

--- On Fri, 2/20/09, A. Kretschmer  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


  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-02-20 Thread Ketema Harris 
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  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  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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-02-20 Thread Eus 
Hi Ho!

--- On Fri, 2/20/09, Miguel Ángel MF  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




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-02-20 Thread Marc Schablewski 
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
>
>
>   
>
>   


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-02-20 Thread Miguel Ángel MF 
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  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
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

2009-02-20 Thread A. Kretschmer 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-02-20 Thread Eus 
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  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

[GENERAL] Why I cannot call a function from within an SQL function?

2009-02-20 Thread 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?

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