[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] Problem with functions
From:       Stephan Szabo <sszabo () megazone ! bigpanda ! com>
Date:       2004-09-27 0:34:29
Message-ID: 20040926172208.C46859 () megazone ! bigpanda ! com
[Download RAW message or body]


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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
[prev in list] [next in list] [prev in thread] [next in thread]