pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate_part stored procs
3+ messages / 2 participants
[nested] [flat]
* date_part stored procs
@ 2004-03-26 14:19 Tsoloane Moahloli <tsoloane@mimecast.co.za>
0 siblings, 1 reply; 3+ messages in thread
From: Tsoloane Moahloli @ 2004-03-26 14:19 UTC (permalink / raw)
To: pgsql-sql
Making me lose my cool. Here is how it goes:
I have the following Stored Proc, Which I intend to find the age in months
of a date...
CREATE FUNCTION interval_months(TIMESTAMP) RETURNS INTEGER AS '
DECLARE
docDate ALIAS FOR $1;
len INTEGER;
BEGIN
SELECT INTO len * FROM (SELECT
((date_part(''year'',age(docDate))*12)+(date_part(''month'',age(docDate))))
AS a;
RETURN len;
END
' LANGUAGE 'plpgsql';
The problem is that it does not run and I cannot for the life of me tell
why.
I run it with the following in psql:
select interval_months(file_date) from (select min(file_date) as file_date
from fileattributes) AS a;
And I get the folllowing error:
NOTICE: Error occurred while executing PL/pgSQL function interval_months
NOTICE: line 5 at select into variables
ERROR: parser: parse error at or near ""
I can't see the error, I've lost my mind.... It's friday........
Tsoloane Moahloli
Verity South Africa (Pty) Ltd
P +27 (11) 475 1718
C +27 (83) 400-2998
<mailto:tsoloane@verity.za.com> tsoloane@verity.za.com
<http://www.verity.com/; http://www.verity.com/
Over 80% of the FORTUNE 50 trust Verity to power their Business Portal and
e-Commerce sites.
Verity is the leader in the Gartner 2002 Enterprise Search Magic Quadrant
Disclaimer
The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of
the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are
hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly
prohibited and may be unlawful. Views and opinions are those of the sender unless clearly stated as being that of Mimecast (Pty) Ltd.
Mimecast (Pty) Ltd is neither liable for the proper, complete transmission of the information contained in this communication, nor any
delay in its receipt or that the mail is virus-free. Mimecast (Pty) Ltd is not liable whatsoever for loss or damage resulting from the opening
of this message and/or attachments and/or the use of the information contained in this message and/or attachments.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: date_part stored procs
@ 2004-03-26 23:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tsoloane Moahloli <tsoloane@mimecast.co.za>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-03-26 23:03 UTC (permalink / raw)
To: tsoloane@mimecast.co.za; +Cc: pgsql-sql
"Tsoloane Moahloli" <tsoloane@mimecast.co.za> writes:
> SELECT INTO len * FROM (SELECT
> ((date_part(''year'',age(docDate))*12)+(date_part(''month'',age(docDate))))
> AS a;
> The problem is that it does not run and I cannot for the life of me tell
> why.
I think you miscounted parentheses.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: date_part stored procs
@ 2004-03-29 07:30 Tsoloane Moahloli <tsoloane@mimecast.co.za>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tsoloane Moahloli @ 2004-03-29 07:30 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Perfect. Thanks a lot....
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Tom Lane
Sent: 27 March 2004 01:03
To: tsoloane@mimecast.co.za
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] date_part stored procs
"Tsoloane Moahloli" <tsoloane@mimecast.co.za> writes:
> SELECT INTO len * FROM (SELECT
> ((date_part(''year'',age(docDate))*12)+(date_part(''month'',age(docDat
> e))))
> AS a;
> The problem is that it does not run and I cannot for the life of me tell
> why.
I think you miscounted parentheses.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Disclaimer
The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of
the individual or entity to whom it is addressed and others authorized to receive it. If you are not the intended recipient you are
hereby notified that any disclosure, copying, distribution or taking action in reliance of the contents of this information is strictly
prohibited and may be unlawful. Views and opinions are those of the sender unless clearly stated as being that of Mimecast (Pty) Ltd.
Mimecast (Pty) Ltd is neither liable for the proper, complete transmission of the information contained in this communication, nor any
delay in its receipt or that the mail is virus-free. Mimecast (Pty) Ltd is not liable whatsoever for loss or damage resulting from the opening
of this message and/or attachments and/or the use of the information contained in this message and/or attachments.
Attachments:
1040329092253034002.gif (image/gif, 4.3K) download
view image
1040329092253037002.gif (image/gif, 10.5K) download
view image
1040329092253040002.gif (image/gif, 812B) download
view image
1040329092253042002.gif (image/gif, 812B) download
view image
end of thread, other threads:[~2004-03-29 07:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-26 14:19 date_part stored procs Tsoloane Moahloli <tsoloane@mimecast.co.za>
2004-03-26 23:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-29 07:30 ` Tsoloane Moahloli <tsoloane@mimecast.co.za>
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