pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction error
14+ messages / 10 participants
[nested] [flat]
* Function error
@ 1999-05-12 13:40 Adam H. Pendleton <apendleton@vgsinc.com>
0 siblings, 0 replies; 14+ messages in thread
From: Adam H. Pendleton @ 1999-05-12 13:40 UTC (permalink / raw)
To: pgsql-sql@hub.org
I created the following function:
create function employee_pay_update() returns opaque as '
begin
insert into employee_payroll values (
new.employee_id,
new.employee_name
);
' language 'plpgsql';
This is just supposed to insert any record inserted into a table into
employee_payroll. I don't need any sort of error checking as that will be
done elsewhere. When I try to insert into the first table, and the trigger
calls this function I get this error:
java.sql.SQLException: ERROR: fmgr_info: function 19265: cache lookup
failed
Did I forget to do something?
Adam
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Si hoc legere scis nimium eruditionis habes.
^ permalink raw reply [nested|flat] 14+ messages in thread
* function error
@ 2002-03-27 19:59 Travis Hoyt <thoyt@npc.net>
0 siblings, 3 replies; 14+ messages in thread
From: Travis Hoyt @ 2002-03-27 19:59 UTC (permalink / raw)
To: pgsql-sql
CREATE FUNCTION biwklyavg(text, text, text) RETURNS numeric AS '
DECLARE
system ALIAS $1;
startdate ALIAS $2;
enddate ALIAS $3;
result numeric;
BEGIN
result := (select (avg(usr) + avg(sys)) from sardata where
systemid = system
and time between startdate and enddate;
END;
' LANGUAGE 'sql';
Hello,
The fuction listed above gives the following error:
ERROR: parser: parse error at or near "alias"
I'm running v7.2. Any ideas? I pulled this directly from the examples in
the PostgreSQL Developer's Handbook.
Thanks,
Travis
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.0K) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: function error
@ 2002-03-27 20:06 PG Explorer <pgmail@pgexplorer.com>
parent: Travis Hoyt <thoyt@npc.net>
2 siblings, 1 reply; 14+ messages in thread
From: PG Explorer @ 2002-03-27 20:06 UTC (permalink / raw)
To: Travis Hoyt <thoyt@npc.net>; pgsql-sql
Needs a FOR!!
system ALIAS FOR $1;
Strange that the sample is wrong?
http://www.pgexplorer.com
----- Original Message -----
From: "Travis Hoyt" <thoyt@npc.net>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, March 27, 2002 9:59 PM
Subject: [SQL] function error
> CREATE FUNCTION biwklyavg(text, text, text) RETURNS numeric AS '
> DECLARE
> system ALIAS $1;
> startdate ALIAS $2;
> enddate ALIAS $3;
> result numeric;
>
> BEGIN
> result := (select (avg(usr) + avg(sys)) from sardata where
> systemid = system
> and time between startdate and enddate;
> END;
> ' LANGUAGE 'sql';
>
> Hello,
>
> The fuction listed above gives the following error:
>
> ERROR: parser: parse error at or near "alias"
>
> I'm running v7.2. Any ideas? I pulled this directly from the examples in
> the PostgreSQL Developer's Handbook.
>
> Thanks,
>
> Travis
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: function error
@ 2002-03-27 20:10 Travis Hoyt <thoyt@npc.net>
parent: PG Explorer <pgmail@pgexplorer.com>
0 siblings, 1 reply; 14+ messages in thread
From: Travis Hoyt @ 2002-03-27 20:10 UTC (permalink / raw)
To: PG Explorer <pgmail@pgexplorer.com>; pgsql-sql
Doh! Sorry my code DOES have the FOR in it as does the example in the
book. Neither works though.
My apologies this was my second attempt w/o the FOR.
Here's what I really have:
CREATE FUNCTION biwklyavg(text, text, text) RETURNS numeric AS '
DECLARE
system ALIAS FOR $1;
startdate ALIAS FOR $2;
enddate ALIAS FOR $3;
result numeric;
BEGIN
result := (select (avg(usr) + avg(sys)) from sardata
where
systemid = system and time between startdate and enddate;
END;
' LANGUAGE 'sql';
-----Original Message-----
From: PG Explorer [mailto:pgmail@pgexplorer.com]
Sent: Wednesday, March 27, 2002 3:06 PM
To: Travis Hoyt; pgsql-sql@postgresql.org
Subject: Re: [SQL] function error
Needs a FOR!!
system ALIAS FOR $1;
Strange that the sample is wrong?
http://www.pgexplorer.com
----- Original Message -----
From: "Travis Hoyt" <thoyt@npc.net>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, March 27, 2002 9:59 PM
Subject: [SQL] function error
> CREATE FUNCTION biwklyavg(text, text, text) RETURNS numeric AS '
> DECLARE
> system ALIAS $1;
> startdate ALIAS $2;
> enddate ALIAS $3;
> result numeric;
>
> BEGIN
> result := (select (avg(usr) + avg(sys)) from sardata
where
> systemid = system
> and time between startdate and enddate;
> END;
> ' LANGUAGE 'sql';
>
> Hello,
>
> The fuction listed above gives the following error:
>
> ERROR: parser: parse error at or near "alias"
>
> I'm running v7.2. Any ideas? I pulled this directly from the examples
in
> the PostgreSQL Developer's Handbook.
>
> Thanks,
>
> Travis
>
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.0K) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: function error
@ 2002-03-27 20:25 Jan Wieck <janwieck@yahoo.com>
parent: Travis Hoyt <thoyt@npc.net>
2 siblings, 0 replies; 14+ messages in thread
From: Jan Wieck @ 2002-03-27 20:25 UTC (permalink / raw)
To: Travis Hoyt <thoyt@npc.net>; +Cc: pgsql-sql
Travis Hoyt wrote:
> CREATE FUNCTION biwklyavg(text, text, text) RETURNS numeric AS '
> DECLARE
> system ALIAS $1;
> startdate ALIAS $2;
> enddate ALIAS $3;
> result numeric;
>
> BEGIN
> result := (select (avg(usr) + avg(sys)) from sardata where
> systemid = system
> and time between startdate and enddate;
> END;
> ' LANGUAGE 'sql';
What about using a language that actually supports the above
syntax, like plpgsql?
Jan
>
> Hello,
>
> The fuction listed above gives the following error:
>
> ERROR: parser: parse error at or near "alias"
>
> I'm running v7.2. Any ideas? I pulled this directly from the examples in
> the PostgreSQL Developer's Handbook.
>
> Thanks,
>
> Travis
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: function error
@ 2002-03-27 20:37 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Travis Hoyt <thoyt@npc.net>
0 siblings, 0 replies; 14+ messages in thread
From: Stephan Szabo @ 2002-03-27 20:37 UTC (permalink / raw)
To: Travis Hoyt <thoyt@npc.net>; +Cc: PG Explorer <pgmail@pgexplorer.com>; pgsql-sql
On Wed, 27 Mar 2002, Travis Hoyt wrote:
> Doh! Sorry my code DOES have the FOR in it as does the example in the
> book. Neither works though.
>
> My apologies this was my second attempt w/o the FOR.
>
> Here's what I really have:
>
> CREATE FUNCTION biwklyavg(text, text, text) RETURNS numeric AS '
> DECLARE
> system ALIAS FOR $1;
> startdate ALIAS FOR $2;
> enddate ALIAS FOR $3;
> result numeric;
>
> BEGIN
> result := (select (avg(usr) + avg(sys)) from sardata
> where
> systemid = system and time between startdate and enddate;
> END;
> ' LANGUAGE 'sql';
I think you want plpgsql not sql as the language.
There does appear to be a missing close paren on the result:= thing as
well.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: function error
@ 2002-03-28 15:35 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: Travis Hoyt <thoyt@npc.net>
2 siblings, 1 reply; 14+ messages in thread
From: Jeff Eckermann @ 2002-03-28 15:35 UTC (permalink / raw)
To: Travis Hoyt <thoyt@npc.net>; pgsql-sql
If this is genuinely a copy of something in the
"PostgreSQL Developer's Handbook", then that book
could use some work.
1. You need "ALIAS FOR" in the DECLARE section
2. You need an explicit RETURN; i.e. write "RETURN
result;" before "END;"
3. Language is plpgsql, not sql (which doesn't allow
the constructions you are using).
If columns "systemid" and "time" are not of text type,
you will need to do some type casting for your
comparisons to work. I believe "time" is a reserved
word anyway, so if that is the real name of the
column, you will need to double-quote it, which in the
function definition will need to be escaped:
'"time"', or \"time\".
--- Travis Hoyt <thoyt@npc.net> wrote:
> CREATE FUNCTION biwklyavg(text, text, text) RETURNS
> numeric AS '
> DECLARE
> system ALIAS $1;
> startdate ALIAS $2;
> enddate ALIAS $3;
> result numeric;
>
> BEGIN
> result := (select (avg(usr) +
> avg(sys)) from sardata where
> systemid = system
> and time between startdate
> and enddate;
> END;
> ' LANGUAGE 'sql';
>
> Hello,
>
> The fuction listed above gives the following error:
>
> ERROR: parser: parse error at or near "alias"
>
> I'm running v7.2. Any ideas? I pulled this
> directly from the examples in
> the PostgreSQL Developer's Handbook.
>
> Thanks,
>
> Travis
>
> ATTACHMENT part 2 application/x-pkcs7-signature
name=smime.p7s
__________________________________________________
Do You Yahoo!?
Yahoo! Movies - coverage of the 74th Academy Awards®
http://movies.yahoo.com/
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: function error
@ 2002-03-28 15:51 Travis Hoyt <thoyt@npc.net>
parent: Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 0 replies; 14+ messages in thread
From: Travis Hoyt @ 2002-03-28 15:51 UTC (permalink / raw)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>; pgsql-sql
My sincerest apologies. I had mistakenly posted a hacked up "let's try
this" version of a function I had created that wasn't working.
The truth is, everything you mentioned below IS in the book and written
very well. I removed the FOR statement, the RETURN statement and
the language definition being SQL instead of PLPGSQL was also my fault.
Additionally, my variable types are wrong (my fault too). So what am I
trying to say...it's all my fault. PostgreSQL Developer's Handbook is
really a great book! It's just dangerous in the hands of idiots like me!
Pause for time to open mouth and insert foot.
Oh the humanity!
Shamefully,
Travis
-----Original Message-----
From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Sent: Thursday, March 28, 2002 10:35 AM
To: Travis Hoyt; pgsql-sql@postgresql.org
Subject: Re: [SQL] function error
If this is genuinely a copy of something in the
"PostgreSQL Developer's Handbook", then that book
could use some work.
1. You need "ALIAS FOR" in the DECLARE section
2. You need an explicit RETURN; i.e. write "RETURN
result;" before "END;"
3. Language is plpgsql, not sql (which doesn't allow
the constructions you are using).
If columns "systemid" and "time" are not of text type,
you will need to do some type casting for your
comparisons to work. I believe "time" is a reserved
word anyway, so if that is the real name of the
column, you will need to double-quote it, which in the
function definition will need to be escaped:
'"time"', or \"time\".
--- Travis Hoyt <thoyt@npc.net> wrote:
> CREATE FUNCTION biwklyavg(text, text, text) RETURNS
> numeric AS '
> DECLARE
> system ALIAS $1;
> startdate ALIAS $2;
> enddate ALIAS $3;
> result numeric;
>
> BEGIN
> result := (select (avg(usr) +
> avg(sys)) from sardata where
> systemid = system
> and time between startdate
> and enddate;
> END;
> ' LANGUAGE 'sql';
>
> Hello,
>
> The fuction listed above gives the following error:
>
> ERROR: parser: parse error at or near "alias"
>
> I'm running v7.2. Any ideas? I pulled this
> directly from the examples in
> the PostgreSQL Developer's Handbook.
>
> Thanks,
>
> Travis
>
> ATTACHMENT part 2 application/x-pkcs7-signature
name=smime.p7s
__________________________________________________
Do You Yahoo!?
Yahoo! Movies - coverage of the 74th Academy Awards.
http://movies.yahoo.com/
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.0K) download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Function error
@ 2002-08-13 06:06 Sugandha Shah <Sugandhas@cybage.com>
0 siblings, 1 reply; 14+ messages in thread
From: Sugandha Shah @ 2002-08-13 06:06 UTC (permalink / raw)
To: pgsql-sql
Hi ,
I'm porting MS- SQL stored procedure to postgres . I'm getting this error :
Error occurred while executing PL/pgSQL function sel_free_disk_space
line 7 at SQL statement
SELECT query has no destination for result data.
If you want to discard the results, use PERFORM instead.
Here is the MS -SQL stored procedure :
CREATE procedure sel_free_disk_space
@computer_id int,
@letter char(1)
as
declare @free int
select @free = [free]
from logical_drive
where computer_id = @computer_id
and letter = upper(@letter)
if (@free is null)
set @free = -1
return @free
GO
Here is the equivalent function for Postgres :
CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer AS '
DECLARE
-- Declare variable to store the free space.
free INTEGER;
BEGIN
select free from logical_drive where computer_id = $1 and letter = upper($2);
IF free IS NULL THEN
RETURN -1;
END IF;
RETURN free;
END;
'LANGUAGE 'plpgsql';
I'm not able to understand what I'm missing ?
Secondly is there any equivalent of exec for postgres ?
Any help will be highly appreciated.
Regards,
-Sugandha
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Function error
@ 2002-08-13 07:01 Janning Vygen <vygen@gmx.de>
parent: Sugandha Shah <Sugandhas@cybage.com>
0 siblings, 1 reply; 14+ messages in thread
From: Janning Vygen @ 2002-08-13 07:01 UTC (permalink / raw)
To: Sugandha Shah <Sugandhas@cybage.com>; pgsql-sql
Am Dienstag, 13. August 2002 08:06 schrieb Sugandha Shah:
> Hi ,
>
> I'm porting MS- SQL stored procedure to postgres . I'm getting this
> error :
>
> Error occurred while executing PL/pgSQL function
> sel_free_disk_space line 7 at SQL statement
> SELECT query has no destination for result data.
> If you want to discard the results, use PERFORM instead.
you need to SELECT INTO if you want to set a varaible in plpgsql
try this:
DECLARE
var_free integer;
-- var_free is used just to name it differently from the column name
BEGIN
SELECT INTO var_free
free from logical_drive where computer_id = $1 and
letter = upper($2);
IF var_free THEN
...
Janning
> CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer
> AS ' DECLARE
> -- Declare variable to store the free space.
> free INTEGER;
>
> BEGIN
> select free from logical_drive where computer_id = $1 and
> letter = upper($2);
> IF free IS NULL THEN
> RETURN -1;
> END IF;
>
> RETURN free;
> END;
> 'LANGUAGE 'plpgsql';
>
>
> I'm not able to understand what I'm missing ?
>
> Secondly is there any equivalent of exec for postgres ?
>
> Any help will be highly appreciated.
>
> Regards,
> -Sugandha
--
Planwerk 6 /websolutions
Herzogstraße 86
40215 Düsseldorf
fon 0211-6015919
fax 0211-6015917
http://www.planwerk6.de
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Function error
@ 2002-08-13 07:28 Sugandha Shah <Sugandhas@cybage.com>
parent: Janning Vygen <vygen@gmx.de>
0 siblings, 1 reply; 14+ messages in thread
From: Sugandha Shah @ 2002-08-13 07:28 UTC (permalink / raw)
To: Janning Vygen <vygen@gmx.de>; +Cc: pgsql-sql
Hi ,
Thanx a lot it worked .
Is there any equivalent of dateadd function in postgres ?
Regards,
-Sugandha
----- Original Message -----
From: "Janning Vygen" <vygen@gmx.de>
To: "Sugandha Shah" <Sugandhas@cybage.com>; <pgsql-sql@postgresql.org>
Sent: Tuesday, August 13, 2002 12:31 PM
Subject: Re: [SQL] Function error
> Am Dienstag, 13. August 2002 08:06 schrieb Sugandha Shah:
> > Hi ,
> >
> > I'm porting MS- SQL stored procedure to postgres . I'm getting this
> > error :
> >
> > Error occurred while executing PL/pgSQL function
> > sel_free_disk_space line 7 at SQL statement
> > SELECT query has no destination for result data.
> > If you want to discard the results, use PERFORM instead.
>
> you need to SELECT INTO if you want to set a varaible in plpgsql
>
> try this:
> DECLARE
> var_free integer;
> -- var_free is used just to name it differently from the column name
> BEGIN
> SELECT INTO var_free
> free from logical_drive where computer_id = $1 and
> letter = upper($2);
> IF var_free THEN
> ...
>
>
> Janning
>
> > CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer
> > AS ' DECLARE
> > -- Declare variable to store the free space.
> > free INTEGER;
> >
> > BEGIN
> > select free from logical_drive where computer_id = $1 and
> > letter = upper($2);
> > IF free IS NULL THEN
> > RETURN -1;
> > END IF;
> >
> > RETURN free;
> > END;
> > 'LANGUAGE 'plpgsql';
> >
> >
> > I'm not able to understand what I'm missing ?
> >
> > Secondly is there any equivalent of exec for postgres ?
> >
> > Any help will be highly appreciated.
> >
> > Regards,
> > -Sugandha
>
> --
> Planwerk 6 /websolutions
> Herzogstraße 86
> 40215 Düsseldorf
>
> fon 0211-6015919
> fax 0211-6015917
> http://www.planwerk6.de
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Function error
@ 2002-08-13 07:31 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Sugandha Shah <Sugandhas@cybage.com>
0 siblings, 0 replies; 14+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-13 07:31 UTC (permalink / raw)
To: Sugandha Shah <Sugandhas@cybage.com>; Janning Vygen <vygen@gmx.de>; +Cc: pgsql-sql
> Hi ,
>
> Thanx a lot it worked .
>
> Is there any equivalent of dateadd function in postgres ?
I highly recommend you actually read the Postgres manual's entries on date
and time manipulation.
You can just add intervals to dates:
SELECT datefield + INTERVAL '1 month';
Chris
^ permalink raw reply [nested|flat] 14+ messages in thread
* FUNCTION ERROR
@ 2003-04-22 21:17 Mario Alberto Soto Cordones <mario_soto@compuall.cl>
0 siblings, 1 reply; 14+ messages in thread
From: Mario Alberto Soto Cordones @ 2003-04-22 21:17 UTC (permalink / raw)
To: pgsql-sql
Hi.
i have a big 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';
when execute this function
select * from get_contenidos(10,1,15,17,31,12)
as (codigo numeric(3,0),
descripcion text)
SAY
ERROR: ExecMakeTableFunctionResult: Invalid result from function returning
tuple
ANY IDEA
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: FUNCTION ERROR
@ 2003-04-22 21:24 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Mario Alberto Soto Cordones <mario_soto@compuall.cl>
0 siblings, 0 replies; 14+ messages in thread
From: Stephan Szabo @ 2003-04-22 21:24 UTC (permalink / raw)
To: Mario Alberto Soto Cordones <mario_soto@compuall.cl>; +Cc: pgsql-sql
On Tue, 22 Apr 2003, Mario Alberto Soto Cordones wrote:
> Hi.
> i have a big 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';
>
> when execute this function
>
> select * from get_contenidos(10,1,15,17,31,12)
> as (codigo numeric(3,0),
> descripcion text)
>
> SAY
>
> ERROR: ExecMakeTableFunctionResult: Invalid result from function returning
> tuple
>
> ANY IDEA
It's hard to say since you're not calling the function you gave the
definition for, the function definition above isn't valid I think (I get
an error at unidades after making a table with appropriate columns -
fixable by replacing typeof with setof), and you didn't give the table
definition for the table in question.
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2003-04-22 21:24 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-12 13:40 Function error Adam H. Pendleton <apendleton@vgsinc.com>
2002-03-27 19:59 function error Travis Hoyt <thoyt@npc.net>
2002-03-27 20:06 ` Re: function error PG Explorer <pgmail@pgexplorer.com>
2002-03-27 20:10 ` Re: function error Travis Hoyt <thoyt@npc.net>
2002-03-27 20:37 ` Re: function error Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-27 20:25 ` Re: function error Jan Wieck <janwieck@yahoo.com>
2002-03-28 15:35 ` Re: function error Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-03-28 15:51 ` Re: function error Travis Hoyt <thoyt@npc.net>
2002-08-13 06:06 Function error Sugandha Shah <Sugandhas@cybage.com>
2002-08-13 07:01 ` Janning Vygen <vygen@gmx.de>
2002-08-13 07:28 ` Sugandha Shah <Sugandhas@cybage.com>
2002-08-13 07:31 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-04-22 21:17 FUNCTION ERROR Mario Alberto Soto Cordones <mario_soto@compuall.cl>
2003-04-22 21:24 ` Re: FUNCTION ERROR Stephan Szabo <sszabo@megazone23.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