From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Michael Snodgrass <michael_snodgrass_us@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem with functions
Date: Sun, 26 Sep 2004 17:34:29 -0700 (PDT)
Message-ID: <20040926172208.C46859@megazone.bigpanda.com> (raw)
In-Reply-To: <20040921013645.2203.qmail@web50804.mail.yahoo.com>
References: <20040921013645.2203.qmail@web50804.mail.yahoo.com>
On Mon, 20 Sep 2004, Michael Snodgrass wrote:
> 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;
That's not the syntax for plpgsql set returning functions. I think
however, you'd likely be able to make it work by using LANGUAGE 'sql'.
If you want more information, there's the set returning function guide on
techdocs:
http://techdocs.postgresql.org/guides/SetReturningFunctions
and a General Bits article at
http://www.varlena.com/varlena/GeneralBits/26.html
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: sszabo@megazone.bigpanda.com, michael_snodgrass_us@yahoo.com
Subject: Re: Problem with functions
In-Reply-To: <20040926172208.C46859@megazone.bigpanda.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