From: Travis Hoyt <thoyt@npc.net>
To: Jeff Eckermann <jeff_eckermann@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: function error
Date: Thu, 28 Mar 2002 10:51:37 -0500
Message-ID: <DBEMKMGOMJAGKAKEPPEGKEDEDHAA.thoyt@npc.net> (raw)
In-Reply-To: <20020328153525.78708.qmail@web20806.mail.yahoo.com>
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
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: thoyt@npc.net, jeff_eckermann@yahoo.com
Subject: Re: function error
In-Reply-To: <DBEMKMGOMJAGKAKEPPEGKEDEDHAA.thoyt@npc.net>
* 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