pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcreate function problem
8+ messages / 6 participants
[nested] [flat]
* create function problem
@ 2002-03-19 08:25 Kancha . <kancha2np@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Kancha . @ 2002-03-19 08:25 UTC (permalink / raw)
To: pgsql-sql
I've used the following sql to create the function and
function creation is also successful.
create function usage(text)
returns numeric
as 'select ceil(sum(sessiontime)/60) from ath_online
where loginname=\'$1\' and startdate >= (select
startdate from cst_package where status=\'t\' and
cid=(select cid from cst_customer where
loginname=\'$1\'));'
language 'sql';
executing this function as
select usage('kancha');
returns no records. while executing the query directly
replacke $1 with kancha returns result. Where did i go
wrong ??
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - live college hoops coverage
http://sports.yahoo.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: create function problem
@ 2002-03-19 08:36 Matteo Beccati <m.beccati@crpsoftware.it>
2002-03-19 10:03 ` Re: create function problem Kancha . <kancha2np@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Matteo Beccati @ 2002-03-19 08:36 UTC (permalink / raw)
To: 'Kancha .' <kancha2np@yahoo.com>; pgsql-sql
Hi,
> create function usage(text)
> returns numeric
> as 'select ceil(sum(sessiontime)/60) from ath_online
> where loginname=\'$1\' and startdate >= (select
> startdate from cst_package where status=\'t\' and
> cid=(select cid from cst_customer where
> loginname=\'$1\'));'
> language 'sql';
Try to replace \'$1\' with just $1 (i.e. loginname = $1).
Regards
--
Matteo Beccati
C.R.P. Software S.r.l.
http://www.crpsoftware.it/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: create function problem
2002-03-19 08:36 Re: create function problem Matteo Beccati <m.beccati@crpsoftware.it>
@ 2002-03-19 10:03 ` Kancha . <kancha2np@yahoo.com>
2002-03-19 14:25 ` Re: create function problem Jan Wieck <janwieck@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kancha . @ 2002-03-19 10:03 UTC (permalink / raw)
To: Matteo Beccati <m.beccati@crpsoftware.it>; pgsql-sql
> Hi,
>
> > create function usage(text)
> > returns numeric
> > as 'select ceil(sum(sessiontime)/60) from
> ath_online
> > where loginname=\'$1\' and startdate >= (select
> > startdate from cst_package where status=\'t\' and
> > cid=(select cid from cst_customer where
> > loginname=\'$1\'));'
> > language 'sql';
>
> Try to replace \'$1\' with just $1 (i.e. loginname =
> $1).
>
the argument is a string so it has to be enclosed in
quotes and \ is to escape the quote. replacing with
just $1 will give error like following:
Unable to identify an operator '=$' for types
'varchar' and 'int4'
You will have to retype this query using an
explicit cast
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - live college hoops coverage
http://sports.yahoo.com/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: create function problem
2002-03-19 08:36 Re: create function problem Matteo Beccati <m.beccati@crpsoftware.it>
2002-03-19 10:03 ` Re: create function problem Kancha . <kancha2np@yahoo.com>
@ 2002-03-19 14:25 ` Jan Wieck <janwieck@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jan Wieck @ 2002-03-19 14:25 UTC (permalink / raw)
To: Kancha . <kancha2np@yahoo.com>; +Cc: Matteo Beccati <m.beccati@crpsoftware.it>; pgsql-sql
Kancha . wrote:
>
> > Hi,
> >
> > > create function usage(text)
> > > returns numeric
> > > as 'select ceil(sum(sessiontime)/60) from
> > ath_online
> > > where loginname=\'$1\' and startdate >= (select
> > > startdate from cst_package where status=\'t\' and
> > > cid=(select cid from cst_customer where
> > > loginname=\'$1\'));'
> > > language 'sql';
> >
> > Try to replace \'$1\' with just $1 (i.e. loginname =
> > $1).
> >
>
> the argument is a string so it has to be enclosed in
> quotes and \ is to escape the quote. replacing with
> just $1 will give error like following:
The argument is a string and $1 becomes a T_Param node of
type "text" in your case, which is a perfectly valid string
without the quotes. Don't get confused by the $ sign in the
parameter name. $1 is treated like a variable name.
Just try "exactly" what was suggested, including the
whitespaces surrounding the = operator. Write
loginname = $1
instead of
loginname=$1
This does not only make the code more readable for humans.
The parser likes it too.
Jan
>
> Unable to identify an operator '=$' for types
> 'varchar' and 'int4'
> You will have to retype this query using an
> explicit cast
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Sports - live college hoops coverage
> http://sports.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
#======================================================================#
# 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] 8+ messages in thread
* create function problem
@ 2010-12-30 16:26 Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-12-30 17:35 ` Re: create function problem Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Gary Stainburn @ 2010-12-30 16:26 UTC (permalink / raw)
To: pgsql-sql
Hi folks,
I'm writing my first plpsql function in ages and I'm going blind trying to see
why it won't create. The error message and the code are below. I'm guessing
it's something blindingly obvious, but can someone please point it out to me.
ta
Gary
The actual line number is the LANGUAGE line at the end of the file.
goole=# \i project_delivery_date.sql
psql:project_delivery_date.sql:42: ERROR: syntax error at or near ";"
LINE 37: END;
^
goole=# \q
[root@stan t-cards]# cat project_delivery_date.sql
-- vim: ft=sql et ai ic
--
-- project_delivery_date() - project delivery date from existing dates
CREATE FUNCTION project_delivery_date(date,date,date,date) RETURNS date AS $$
DECLARE
eta_dealer ALIAS FOR $1;
eta_customer ALIAS FOR $2;
req_date ALIAS FOR $3;
act_date ALIAS FOR $4;
eta date;
BEGIN
IF act_date IS NOT NULL THEN
return act_date;
END IF;
IF eta_dealer IS NOT NULL AND eta_customer IS NULL THEN
eta := eta_dealer;
ELSE IF eta_dealer IS NULL AND eta_customer IS NOT NULL THEN
eta := eta_customer;
ELSE IF eta_dealer IS NULL AND eta_customer IS NULL THEN
eta := NULL;
ELSE IF eta_dealer > eta_customer THEN
eta := eta_dealer;
ELSE
eta := eta_customer;
END IF;
IF eta IS NOT NULL AND req_date IS NULL THEN
RETURN eta;
END IF;
IF eta IS NULL AND req_date IS NOT NULL THEN
RETURN req_date;
END IF;
IF eta IS NULL AND req_date IS NULL THEN
RETURN NULL;
END IF;
IF eta > req_date THEN
RETURN eta;
END IF;
RETURN req_date;
END;
$$ LANGUAGE 'plpgsql';
[root@stan t-cards]#
--
Gary Stainburn
I.T. Manager
Ringways Garages
http://www.ringways.co.uk
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: create function problem
2010-12-30 16:26 create function problem Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2010-12-30 17:35 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-12-30 17:47 ` Re: create function problem Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Pavel Stehule @ 2010-12-30 17:35 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Hello
you badly use a IF statement. It's not C. Every IF must to finish with END IF
this is
IF .. THEN
ELSEIF .. THEN ..
ELSE
END IF
Regards
Pavel Stehule
2010/12/30 Gary Stainburn <gary.stainburn@ringways.co.uk>:
> Hi folks,
>
> I'm writing my first plpsql function in ages and I'm going blind trying to see
> why it won't create. The error message and the code are below. I'm guessing
> it's something blindingly obvious, but can someone please point it out to me.
>
> ta
>
> Gary
>
> The actual line number is the LANGUAGE line at the end of the file.
>
> goole=# \i project_delivery_date.sql
> psql:project_delivery_date.sql:42: ERROR: syntax error at or near ";"
> LINE 37: END;
> ^
> goole=# \q
> [root@stan t-cards]# cat project_delivery_date.sql
> -- vim: ft=sql et ai ic
> --
> -- project_delivery_date() - project delivery date from existing dates
>
> CREATE FUNCTION project_delivery_date(date,date,date,date) RETURNS date AS $$
> DECLARE
> eta_dealer ALIAS FOR $1;
> eta_customer ALIAS FOR $2;
> req_date ALIAS FOR $3;
> act_date ALIAS FOR $4;
> eta date;
>
> BEGIN
> IF act_date IS NOT NULL THEN
> return act_date;
> END IF;
> IF eta_dealer IS NOT NULL AND eta_customer IS NULL THEN
> eta := eta_dealer;
> ELSE IF eta_dealer IS NULL AND eta_customer IS NOT NULL THEN
> eta := eta_customer;
> ELSE IF eta_dealer IS NULL AND eta_customer IS NULL THEN
> eta := NULL;
> ELSE IF eta_dealer > eta_customer THEN
> eta := eta_dealer;
> ELSE
> eta := eta_customer;
> END IF;
> IF eta IS NOT NULL AND req_date IS NULL THEN
> RETURN eta;
> END IF;
> IF eta IS NULL AND req_date IS NOT NULL THEN
> RETURN req_date;
> END IF;
> IF eta IS NULL AND req_date IS NULL THEN
> RETURN NULL;
> END IF;
> IF eta > req_date THEN
> RETURN eta;
> END IF;
> RETURN req_date;
> END;
> $$ LANGUAGE 'plpgsql';
>
> [root@stan t-cards]#
>
> --
> Gary Stainburn
> I.T. Manager
> Ringways Garages
> http://www.ringways.co.uk
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: create function problem
2010-12-30 16:26 create function problem Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-12-30 17:35 ` Re: create function problem Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-12-30 17:47 ` Adrian Klaver <adrian.klaver@gmail.com>
2010-12-30 20:16 ` Re: create function problem Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2010-12-30 17:47 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
On 12/30/2010 09:35 AM, Pavel Stehule wrote:
> Hello
>
> you badly use a IF statement. It's not C. Every IF must to finish with END IF
>
> this is
>
> IF .. THEN
> ELSEIF .. THEN ..
> ELSE
> END IF
>
> Regards
>
> Pavel Stehule
To follow up I think the OP was looking for ELSEIF not ELSE IF. Changing
the ELSE IF to ELSEIF should fix it.
--
Adrian Klaver
adrian.klaver@gmail.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: create function problem
2010-12-30 16:26 create function problem Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-12-30 17:35 ` Re: create function problem Pavel Stehule <pavel.stehule@gmail.com>
2010-12-30 17:47 ` Re: create function problem Adrian Klaver <adrian.klaver@gmail.com>
@ 2010-12-30 20:16 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 8+ messages in thread
From: Gary Stainburn @ 2010-12-30 20:16 UTC (permalink / raw)
To: pgsql-sql
On Thursday 30 December 2010 17:47:09 Adrian Klaver wrote:
> To follow up I think the OP was looking for ELSEIF not ELSE IF. Changing
> the ELSE IF to ELSEIF should fix it.
>
Thanks guys. That'll teach me to read tutorials more carefully.
--
Gary Stainburn
I.T. Manager
Ringways Garages
http://www.ringways.co.uk
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2010-12-30 20:16 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-19 08:25 create function problem Kancha . <kancha2np@yahoo.com>
2002-03-19 08:36 Re: create function problem Matteo Beccati <m.beccati@crpsoftware.it>
2002-03-19 10:03 ` Kancha . <kancha2np@yahoo.com>
2002-03-19 14:25 ` Jan Wieck <janwieck@yahoo.com>
2010-12-30 16:26 create function problem Gary Stainburn <gary.stainburn@ringways.co.uk>
2010-12-30 17:35 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-12-30 17:47 ` Adrian Klaver <adrian.klaver@gmail.com>
2010-12-30 20:16 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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