Re: [SQL] empty set
On Thu, Jun 08, 2006 at 14:40:12 -0700, CG <[EMAIL PROTECTED]> wrote: > PostgreSQL 8.1 > > I've been trying to write a SQL prepare routine. One of the challenging > elements I'm running into is an empty set ... > > "select foo from bar where foo in ? ;" > > What if "?" is an set with zero elements? What is the proper value to use to > replace "?" indicating an empty set? Something like the following suggests you can put a subquery there that returns 0 rows. I don't think that wil work for prepared queries though; so it may not help you. bruno=> select * from test where test in (select 1 where false); test -- (0 rows) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] empty set
"select 1 where false" does indeed indicate an empty set. I was hoping for something more elegant, but I'll take what I can get. :) --- Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Thu, Jun 08, 2006 at 14:40:12 -0700, > CG <[EMAIL PROTECTED]> wrote: > > PostgreSQL 8.1 > > > > I've been trying to write a SQL prepare routine. One of the challenging > > elements I'm running into is an empty set ... > > > > "select foo from bar where foo in ? ;" > > > > What if "?" is an set with zero elements? What is the proper value to use > to > > replace "?" indicating an empty set? > > Something like the following suggests you can put a subquery there that > returns > 0 rows. I don't think that wil work for prepared queries though; so it may > not > help you. > > bruno=> select * from test where test in (select 1 where false); > test > -- > (0 rows) > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Problems Testing User-Defined Function
Hi list,
I'm a newbie and I have a problem. I've defined the following function using
pgAdmin but I am clueless as to how I can test it. I will eventually be
calling this function from a .NET application but I want to test it using
raw SQL first. Here's the function definition:
CREATE OR REPLACE FUNCTION sp_insert_manifest(_sender varchar(255),
_sender_email varchar(255),
_reply_to varchar(255),
_filename varchar(255),
_file oid,
_datetime_sent timestamp) RETURNS integer AS $$
-- blah blah --
$$ LANGUAGE plpgsql;
I am trying to pass the following values to the function but I have been so
far unsuccessful.
SELECT sp_insert_manifest('me', [EMAIL PROTECTED]', '[EMAIL PROTECTED]',
'test.txt', NULL,
'2006/06/09')
Can anyone help me here?
Thanks,
Rommel the iCeMAn.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problems Testing User-Defined Function
- Original Message -
From: "Rommel the iCeMAn" <[EMAIL PROTECTED]>
I am trying to pass the following values to the function but I have been
so
far unsuccessful.
What error message are you receiving?
SELECT sp_insert_manifest('me', [EMAIL PROTECTED]', '[EMAIL PROTECTED]', 'test.txt',
NULL,
'2006/06/09')
Is this a direct paste? If so, you are missing the apostrophe before me@
you.com.
Regards,
George
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Problems Testing User-Defined Function
You will have to use the "CALLED ON NULL INPUT" option to "create function"
(Postgresql 8.1, I don't know about other
versions) if you expect NULL arguments.
Jim
-- Original Message ---
From: "Rommel the iCeMAn" <[EMAIL PROTECTED]>
To: "PostgreSQL SQL Mailing List"
Sent: Fri, 9 Jun 2006 16:01:26 -0400
Subject: [SQL] Problems Testing User-Defined Function
> Hi list,
>
> I'm a newbie and I have a problem. I've defined the following function using
> pgAdmin but I am clueless as to how I can test it. I will eventually be
> calling this function from a .NET application but I want to test it using
> raw SQL first. Here's the function definition:
>
> CREATE OR REPLACE FUNCTION sp_insert_manifest(_sender varchar(255),
>_sender_email varchar(255),
>_reply_to varchar(255),
>_filename varchar(255),
>_file oid,
>_datetime_sent timestamp) RETURNS integer AS $$
>
> -- blah blah --
>
> $$ LANGUAGE plpgsql;
>
> I am trying to pass the following values to the function but I have been so
> far unsuccessful.
>
> SELECT sp_insert_manifest('me', [EMAIL PROTECTED]', '[EMAIL PROTECTED]',
> 'test.txt', NULL,
> '2006/06/09')
>
> Can anyone help me here?
>
> Thanks,
> Rommel the iCeMAn.
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
--- End of Original Message ---
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problems Testing User-Defined Function
Hi guys,
Thanks for the input so far. No it wasn't a direct paste, what I did in fact
type was SELECT sp_insert_manifest('me', '[EMAIL PROTECTED]', '[EMAIL
PROTECTED]',
'test.txt', NULL, '2006/06/09'). The error message said something like
function sp_insert_manifest(character varying, character varying, character
varying, character varying, "unknown", timestamp) does not exist. It seems
NOT to like my NULL parameter.
I'll investigate the "CALLED ON NULL INPUT" option that Jim suggested.
Thanks again,
Rommel Edwards.
---(end of broadcast)---
TIP 6: explain analyze is your friend
