pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPROBLEM WITH FUNCTIONS
6+ messages / 5 participants
[nested] [flat]
* PROBLEM WITH FUNCTIONS
@ 2003-04-21 21:38 Mario Alberto Soto Cordones <mario_soto@compuall.cl>
0 siblings, 1 reply; 6+ messages in thread
From: Mario Alberto Soto Cordones @ 2003-04-21 21:38 UTC (permalink / raw)
To: pgsql-sql
Hi.
i have a problem with a function.
i create a type unidades
CREATE TYPE unidades AS (codigo numeric(3,0),
descripcion text);
and i create a function get_unidades
CREATE FUNCTION get_unidades(int4, int4, int4, int4) RETURNS typeof
unidades AS '
SELECT cucoduni, cudesuni
FROM cumauni
WHERE cuempuni = $1 and
cuseduni = $2 and
cucaruni = $3 and
cuasiuni= $4
; ' LANGUAGE 'sql';
and at this moment´s it´s all ok BUT when execute this function
select get_unidades(10,1,15,17)
say
"cannot display a value of type RECORD"
any idea, PLEASE HELP MY
saludos
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PROBLEM WITH FUNCTIONS
@ 2003-04-21 21:46 Randall Lucas <rlucas@tercent.net>
parent: Mario Alberto Soto Cordones <mario_soto@compuall.cl>
0 siblings, 1 reply; 6+ messages in thread
From: Randall Lucas @ 2003-04-21 21:46 UTC (permalink / raw)
To: Mario Alberto Soto Cordones <mario_soto@compuall.cl>; +Cc: pgsql-sql
Mario,
If your function returns a record, you have to treat it like it's a
relation (table). So you don't select it, you select from it.
Try:
select * from get_unidades(10, 1, 15, 17);
Also you may have to give it an explicit variable list such as:
select * from get_unidades(10,1,15,17) as (codigo numeric(3,0),
descripcion text);
Best,
Randall
On Monday, April 21, 2003, at 05:38 PM, Mario Alberto Soto Cordones
wrote:
> Hi.
>
> i have a problem with a function.
>
> i create a type unidades
> CREATE TYPE unidades AS (codigo numeric(3,0),
> descripcion text);
>
> and i create a function get_unidades
> CREATE FUNCTION get_unidades(int4, int4, int4, int4) RETURNS
> typeof
> unidades AS '
> SELECT cucoduni, cudesuni
> FROM cumauni
> WHERE cuempuni = $1 and
> cuseduni = $2 and
> cucaruni = $3 and
> cuasiuni= $4
> ; ' LANGUAGE 'sql';
>
> and at this moment´s it´s all ok BUT when execute this function
> select get_unidades(10,1,15,17)
>
> say
> "cannot display a value of type RECORD"
>
> any idea, PLEASE HELP MY
>
> saludos
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PROBLEM WITH FUNCTIONS
@ 2003-04-21 21:58 Mario Alberto Soto Cordones <mario_soto@compuall.cl>
parent: Randall Lucas <rlucas@tercent.net>
0 siblings, 0 replies; 6+ messages in thread
From: Mario Alberto Soto Cordones @ 2003-04-21 21:58 UTC (permalink / raw)
To: rlucas@tercent.net; +Cc: pgsql-sql
Thank Randall
this select * from get_unidades(10, 1, 15, 17);
work very good
thank, very much
Muchas, Muchas gracias
un gran saludo desde chile
Mario
> Mario,
>
> If your function returns a record, you have to treat it like it's a
> relation (table). So you don't select it, you select from it.
>
> Try:
>
> select * from get_unidades(10, 1, 15, 17);
>
> Also you may have to give it an explicit variable list such as:
>
> select * from get_unidades(10,1,15,17) as (codigo numeric(3,0),
> descripcion text);
>
> Best,
>
> Randall
>
> On Monday, April 21, 2003, at 05:38 PM, Mario Alberto Soto Cordones
> wrote:
>
>> Hi.
>>
>> i have a problem with a function.
>>
>> i create a type unidades
>> CREATE TYPE unidades AS (codigo numeric(3,0),
>> descripcion text);
>>
>> and i create a function get_unidades
>> CREATE FUNCTION get_unidades(int4, int4, int4, int4) RETURNS
>> typeof
>> unidades AS '
>> SELECT cucoduni, cudesuni
>> FROM cumauni
>> WHERE cuempuni = $1 and
>> cuseduni = $2 and
>> cucaruni = $3 and
>> cuasiuni= $4
>> ; ' LANGUAGE 'sql';
>>
>> and at this moment´s it´s all ok BUT when execute this function
>> select get_unidades(10,1,15,17)
>>
>> say
>> "cannot display a value of type RECORD"
>>
>> any idea, PLEASE HELP MY
>>
>> saludos
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Mario Soto Cordones
Gerente de Proyectos
Compuall Ingeniería Ltda.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Problem with functions.
@ 2003-12-16 18:55 Jonas Lindholm <jonas.lindholm@omgroup.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jonas Lindholm @ 2003-12-16 18:55 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'n trying to create a function with two input values and returning a SETOF.
When I only have one input value everything is working ok.
If I instead create the same function with two input values the query fail
with:
ERROR: a column definition list is required for functions returning
"record"
The two functions are returning exactly the same type of data.
This is the definition of the function with one input value that works ok:
CREATE FUNCTION getlosts (timestamp) RETURNS SETOF userlog AS '
DECLARE
beg ALIAS FOR $1;
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM userlog WHERE login > beg LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
This is the definition of the function that fail:
(note that I only added the secondary timestamp used to limit the upper
value of the select)
CREATE FUNCTION getlosts (timestamp, timestamp) RETURNS SETOF userlog AS '
DECLARE
beg ALIAS FOR $1;
ending ALIAS FOR $2;
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM userlog WHERE login > beg AND login < ending LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
This is the definition of table userlog:
Table "public.userlog"
Column | Type | Modifiers
----------+--------------------------+-----------
login | timestamp with time zone |
logout | timestamp with time zone |
reason | character varying(20) |
source | character varying(32) |
username | character varying(64) |
gw | character varying(32) |
ident | character varying(64) |
id | integer |
I'm running Postgres version 7.4.
Thanks
/Jonas L.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Problem with functions
@ 2004-09-21 01:36 Michael Snodgrass <michael_snodgrass_us@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Snodgrass @ 2004-09-21 01:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with functions
@ 2004-09-27 00:34 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Michael Snodgrass <michael_snodgrass_us@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2004-09-27 00:34 UTC (permalink / raw)
To: Michael Snodgrass <michael_snodgrass_us@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-09-27 00:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-21 21:38 PROBLEM WITH FUNCTIONS Mario Alberto Soto Cordones <mario_soto@compuall.cl>
2003-04-21 21:46 ` Randall Lucas <rlucas@tercent.net>
2003-04-21 21:58 ` Mario Alberto Soto Cordones <mario_soto@compuall.cl>
2003-12-16 18:55 Problem with functions. Jonas Lindholm <jonas.lindholm@omgroup.com>
2004-09-21 01:36 Problem with functions Michael Snodgrass <michael_snodgrass_us@yahoo.com>
2004-09-27 00:34 ` Re: Problem with functions Stephan Szabo <sszabo@megazone.bigpanda.com>
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