From: Michael Snodgrass <michael_snodgrass_us@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Problem with functions
Date: Mon, 20 Sep 2004 18:36:45 -0700 (PDT)
Message-ID: <20040921013645.2203.qmail@web50804.mail.yahoo.com> (raw)
I been trying to create several different functions
that mimic several stored procedures in MSSQL.
The SQL works fine in an interactive SQL, but fails
miserably when I try to run/execute a function created
using that same SQL.
I have been searching web sites and reading what I
find but I have not anything on functions that speaks
about the issue I'm having.
I'm hoping maybe you can provide some input on how to
get these functions to work. I am including one of my
functions as sample.
Here's the function:
CREATE OR REPLACE FUNCTION "public"."getappts" (text,
date, date)
RETURNS SETOF "public"."calendar" AS'
SELECT *
FROM calendar
WHERE
event_date BETWEEN $2 AND $3
AND agent_number IN
( SELECT agent_number FROM agents
WHERE rel30a = $1 OR
agent_number = $1 )
ORDER BY event_start_time;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER;
Yes, I need to return multiple rows in the result set.
The two tables have the 'agent_number' column in
common, ie it is really a foreign key. But it is not
set up that way, it's not my database and I can not
change it.
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michael_snodgrass_us@yahoo.com
Subject: Re: Problem with functions
In-Reply-To: <20040921013645.2203.qmail@web50804.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox