Thread: User defined function
I need to write a function to return the first of december of the year
previous to the current year. Part of my problem is I'm not sure how
to escape quotes in function definitions. I am looking at something
along the lines of
CREATE FUNCTION startofregyear(date) RETURNS date AS '
SELECT text_datetime('01/12/' ¦¦ float8_text(datepart('year',$1)-1))
AS answer;
' LANGUAGE 'SQL';
but clearly this won't work because of the large number of single
quotes. I've tried escaping the quotes in the SELECT line with
backslashes, but that doesn't seem to do it.
Can anyone tell me if this function should work, once I sort out the
escaping?
Thanks,
Moray
----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
> I need to write a function to return the first of december of the year
> previous to the current year. Part of my problem is I'm not sure how
> to escape quotes in function definitions. I am looking at something
> along the lines of
>
> CREATE FUNCTION startofregyear(date) RETURNS date AS '
> SELECT text_datetime('01/12/' �� float8_text(datepart('year',$1)-1))
> AS answer;
> ' LANGUAGE 'SQL';
>
> but clearly this won't work because of the large number of single
> quotes. I've tried escaping the quotes in the SELECT line with
> backslashes, but that doesn't seem to do it.
>
> Can anyone tell me if this function should work, once I sort out the
> escaping?
>
try using double quotas...
CREATE FUNCTION startofregyear(date) RETURNS date AS '
SELECT text_datetime(''01/12/'' float8_text(datepart(''year'',$1)-1))
AS answer;
' LANGUAGE 'SQL';
It should work...
--Greg--
> I need to write a function to return the first of december of the
year
> previous to the current year. Part of my problem is I'm not sure how
> to escape quotes in function definitions. I am looking at something
> along the lines of
>
> CREATE FUNCTION startofregyear(date) RETURNS date AS '
> SELECT text_datetime('01/12/' ¦¦ float8_text(datepart('year',$1)-1))
> AS answer;
> ' LANGUAGE 'SQL';
>
> but clearly this won't work because of the large number of single
> quotes. I've tried escaping the quotes in the SELECT line with
> backslashes, but that doesn't seem to do it.
>
> Can anyone tell me if this function should work, once I sort out the
> escaping?
>
try using double quotas...
> CREATE FUNCTION startofregyear(date) RETURNS date AS '
> SELECT text_datetime(''01/12/''
float8_text(datepart(''year'',$1)-1))
> AS answer;
> ' LANGUAGE 'SQL';
absolutely not, I tried that already.
I get "attribute not found" error wherever the opening double-quotes
are during the create of the function.
----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
hi...
>
> > CREATE FUNCTION startofregyear(date) RETURNS date AS '
> > SELECT text_datetime(''01/12/''
> float8_text(datepart(''year'',$1)-1))
> > AS answer;
> > ' LANGUAGE 'SQL';
>
> absolutely not, I tried that already.
> I get "attribute not found" error wherever the opening double-quotes
> are during the create of the function.
try this (it works quite nicely):
select (''12-1-'' || date_part(''year'',$1) - 1)::date;
your problem is 2 fold. first, you need double quotes. second, it isn't
datepart, its date_part. that's the "attribute not found" error.
as a suggestion, try doing the select statement from the command line outside
of a function first to make sure it works. i.e:
select ('12-1-' || date_part('year','now'::date) - 1)::date;
once you know that your select works, pop it into the function. functions don't
return the most helpful error messages =) the command line in psql is much
better for debugging selects/inserts/etc/etc...
--
Aaron J. Seigo
Sys Admin
At 16:41 +0200 on 15/10/1999, Moray McConnachie wrote:
> absolutely not, I tried that already.
> I get "attribute not found" error wherever the opening double-quotes
> are during the create of the function.
Not double quotes (The character "), but two single quotes one after the other.
Anyway, I would take a wholly different approach to this. I don't like
manipulating text and relying on the date format. I prefer working only
with dates and times. So I would use something like this:
testing=> CREATE FUNCTION dec_first( date ) RETURNS date as '
testing'> SELECT date( date_trunc( ''year'', $1 ) - ''1 month''::timespan )'
testing-> LANGUAGE 'sql';
CREATE
testing=> select dec_first('1980-08-14');
dec_first
----------
12-01-1979
(1 row)
testing=> select dec_first('1996-08-15');
dec_first
----------
12-01-1995
(1 row)
testing=> select dec_first('2004-02-14');
dec_first
----------
12-01-2003
(1 row)
Note how my doubled-quotes worked well?
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
Message-ID: <EXECMAIL.991019081832.A@moraypc.computing-services.oxford.ac.uk> Priority: NORMAL X-Mailer: Execmail for Win32 5.1 Build (9) MIME-Version: 1.0 Content-Type: Text/Plain; charset="us-ascii" On Sun, 17 Oct 1999 18:52:58 +0200 Herouth Maoz <herouth@oumail.openu.ac.il> wrote: Belated thanks to Aaron, Greg, Herouth & Stephanie for helping me out with my problem with quoting in functions. Particularly to Herouth for giving me an answer based on date functions - I'm so used to handling time as text from Perl that I didn't even think about it! I have now switched to a mailer that allows me to distinguish more easily between doubled single quotes and double quotes :-> M. ---------------------- Moray.McConnachie@computing-services.oxford.ac.uk
I don't see implement SELECT DISTINCT in views on the To Do list?
I'm using 6.5.2, and on doing
CREATE VIEW blahview AS SELECT DISTINCT bleurgh,blaagh FROM blah;
I get
ERROR: DISTINCT not supported in views.
However, the \h command in psql gives
CREATE VIEW view_name AS
SELECT [DISTINCT [ON attrN]]
etc.
I can't find the bug report form on the WWW to submit a bug report for
psql on this. The FAQ merely says
1.13) How do I submit a bug report?
Fill out the "bug-template" file and send it to: bugs@postgreSQL.org
No indication of how to get hold of the bug-template file...
Thanks,
Moray
----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
----- Original Message -----
From: Aaron J. Seigo <aaron@gtv.ca>
To: Moray McConnachie
<moray.mcconnachie@computing-services.oxford.ac.uk>;
<greg@proterians.net>
Cc: <pgsql-general@postgreSQL.org>
Sent: Friday, October 15, 1999 7:58 PM
Subject: Re: [GENERAL] User defined function
> hi...
>
> >
> > > CREATE FUNCTION startofregyear(date) RETURNS date AS '
> > > SELECT text_datetime(''01/12/''
> > float8_text(datepart(''year'',$1)-1))
> > > AS answer;
> > > ' LANGUAGE 'SQL';
> >
> > absolutely not, I tried that already.
> > I get "attribute not found" error wherever the opening
double-quotes
> > are during the create of the function.
>
> try this (it works quite nicely):
> select (''12-1-'' || date_part(''year'',$1) - 1)::date;
>
> your problem is 2 fold. first, you need double quotes. second, it
isn't
> datepart, its date_part. that's the "attribute not found" error.
>
> as a suggestion, try doing the select statement from the command
line outside
> of a function first to make sure it works. i.e:
> select ('12-1-' || date_part('year','now'::date) - 1)::date;
>
> once you know that your select works, pop it into the function.
functions don't
> return the most helpful error messages =) the command line in psql
is much
> better for debugging selects/inserts/etc/etc...
>
>
> --
> Aaron J. Seigo
> Sys Admin
>
> ************
>
>
On Fri, 22 Oct 1999, Moray McConnachie wrote: > I don't see implement SELECT DISTINCT in views on the To Do list? > > I'm using 6.5.2, and on doing > CREATE VIEW blahview AS SELECT DISTINCT bleurgh,blaagh FROM blah; > > I get > ERROR: DISTINCT not supported in views. > > However, the \h command in psql gives > CREATE VIEW view_name AS > SELECT [DISTINCT [ON attrN]] > etc. > > I can't find the bug report form on the WWW to submit a bug report for > psql on this. The FAQ merely says > > 1.13) How do I submit a bug report? > > Fill out the "bug-template" file and send it to: bugs@postgreSQL.org > > No indication of how to get hold of the bug-template file... > $ locate bug.template /usr/doc/postgresql-6.5.2/bug.template /usr/src/pgsql/postgresql-6.5/doc/bug.template So it looks like the doc directory of your source distribution tree might be a good place to start looking. Or just do a find / -name bug.template -print. -- Bob Kline mailto:bkline@rksystems.com http://www.rksystems.com
> > I can't find the bug report form on the WWW to submit a bug report for > > psql on this. The FAQ merely says > > > > 1.13) How do I submit a bug report? > > > > Fill out the "bug-template" file and send it to: bugs@postgreSQL.org > > > > No indication of how to get hold of the bug-template file... > > > > $ locate bug.template > /usr/doc/postgresql-6.5.2/bug.template > /usr/src/pgsql/postgresql-6.5/doc/bug.template > > So it looks like the doc directory of your source distribution tree > might be a good place to start looking. Or just do a find / -name > bug.template -print. Thanks - perhaps the location of the file should be in the FAQ also? ---------------------------------------------------------------------- ---------------- Moray.McConnachie@computing-services.oxford.ac.uk
On Fri, 22 Oct 1999, Moray McConnachie wrote: > > > > I can't find the bug report form on the WWW to submit a bug report > for > > > psql on this. The FAQ merely says > > > > > > 1.13) How do I submit a bug report? > > > > > > Fill out the "bug-template" file and send it to: > bugs@postgreSQL.org > > > > > > No indication of how to get hold of the bug-template file... > > > > > > > $ locate bug.template > > /usr/doc/postgresql-6.5.2/bug.template > > /usr/src/pgsql/postgresql-6.5/doc/bug.template > > > > So it looks like the doc directory of your source distribution tree > > might be a good place to start looking. Or just do a find / -name > > bug.template -print. > > Thanks - perhaps the location of the file should be in the FAQ also? Sounds reasonable to me. In fact, when I went to check the FAQ to verify that it wasn't already there I noticed that the link to the FAQ from http://www.postgresql.org/info.html was dead. Best might be a link from the FAQ to a copy of the latest version of bug.template. Finally, I noticed that the bug template for has the instructions: You may also enter a bug report at http://www.postgresql.org/ instead of e-mailing this form. But it's not apparent from the home page where to go to do so (in fact, I still haven't found it). And *really* finally, when I went wandering around the site looking for this online bug report page, I kept getting error dialogs popping up, asking if I wanted to debug the page, so I gather there's some fancy client-side scripting going on (click on Development from the home page) that's not quite ready for prime time. :->} That's why I've copied the webmaster on this reply. Cheers! -- Bob Kline mailto:bkline@rksystems.com http://www.rksystems.com
On Fri, 22 Oct 1999, Bob Kline wrote: > Sounds reasonable to me. In fact, when I went to check the FAQ to > verify that it wasn't already there I noticed that the link to the FAQ > from http://www.postgresql.org/info.html was dead. Best might be a link > from the FAQ to a copy of the latest version of bug.template. Where did you get that url? I just went to Info Central and check on each of the FAQs and it appears that the only one having problems is the Japanese version which is hosted on a site in Japan - I'll remove that link. It appears you're looking at an old site or using old urls? If you're accessing a mirror site, could you give me the sitename so I can remove it if it's not up to date? Vince - Webmaster. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null # include <std/disclaimers.h> Have you seen http://www.pop4.net? Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
On Fri, 22 Oct 1999, Vince Vielhaber wrote: > On Fri, 22 Oct 1999, Bob Kline wrote: > > > Sounds reasonable to me. In fact, when I went to check the FAQ to > > verify that it wasn't already there I noticed that the link to the FAQ > > from http://www.postgresql.org/info.html was dead. Best might be a link > > from the FAQ to a copy of the latest version of bug.template. > > Where did you get that url? I just went to Info Central and check on > each of the FAQs and it appears that the only one having problems is > the Japanese version which is hosted on a site in Japan - I'll remove > that link. It appears you're looking at an old site or using old urls? > If you're accessing a mirror site, could you give me the sitename so I > can remove it if it's not up to date? > > Vince - Webmaster. > How to repeat: 1. Start at http://www.postgresql.org/ 2. Click Refresh 3. Click on Info Central from left 4. Click Refresh 5. Click General Info 6. Click Refresh 7. Click "guide" under the heading Info Sheet 8. Click Refresh 9. Click PostgreSQL FAQ (below the first paragraph) The URL after step 8 is http://www.postgresql.org/info.html (as cited in the original report). Hope this helps. -- Bob Kline mailto:bkline@rksystems.com http://www.rksystems.com
On Fri, 22 Oct 1999, Bob Kline wrote: > How to repeat: > 1. Start at http://www.postgresql.org/ > 2. Click Refresh > 3. Click on Info Central from left > 4. Click Refresh > 5. Click General Info > 6. Click Refresh > 7. Click "guide" under the heading Info Sheet > 8. Click Refresh > 9. Click PostgreSQL FAQ (below the first paragraph) > > The URL after step 8 is http://www.postgresql.org/info.html (as cited in > the original report). Go back to step 8 and try it again. It's fixed. BTW, if you look at the documentation page from info central you'll see more than just the one FAQ. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null # include <std/disclaimers.h> Have you seen http://www.pop4.net? Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
On Fri, 22 Oct 1999, Vince Vielhaber wrote: > On Fri, 22 Oct 1999, Bob Kline wrote: > > > How to repeat: > > 1. Start at http://www.postgresql.org/ > > 2. Click Refresh > > 3. Click on Info Central from left > > 4. Click Refresh > > 5. Click General Info > > 6. Click Refresh > > 7. Click "guide" under the heading Info Sheet > > 8. Click Refresh > > 9. Click PostgreSQL FAQ (below the first paragraph) > > > > The URL after step 8 is http://www.postgresql.org/info.html (as cited in > > the original report). > > Go back to step 8 and try it again. It's fixed. Good, thanks. > BTW, if you look at the documentation page from info central you'll > see more than just the one FAQ. > Sure, the FAQ pages aren't hard to find. I was just wandering around trying to see if the answer to the original question (about the location of the bug report form) might have been easily available (apparently not) and I stumbled on the broken link, which I was just reporting as a minor side problem I thought you'd like to know about. Same for the script bugs. Cheers. -- Bob Kline mailto:bkline@rksystems.com http://www.rksystems.com