Solved

Next Working Day PLPGSQL Function

Posted on 2008-06-16
3
576 Views
Last Modified: 2008-06-17
I am trying to write a PLPGSQL function which will return the next working day for a given date. However when I try to run the query:

select next_working_day('16/06/2008'); it gives me the error
ERROR:  syntax error at or near "$1" at character 37
I have tried different date formats incase it was wanting it in the format yyyy/mm/dd etc but still the same error
Can anyone see where I am going wrong here?
DECLARE

       dowCheck RECORD;

       timeCheck ALIAS FOR $1;

       nextWorkingDay INTEGER;

BEGIN
 

SELECT extract(dow FROM TIMESTAMP timeCheck) AS dow INTO dowCheck;
 

IF dowCheck.dow>4 THEN

   dowCheck.dow = 1;

ELSE

    dowCheck.dow = (dowCheck.dow + 1);

END IF;

return dowCheck.dow;

END;

Open in new window

0
Comment
Question by:tricl
  • 2
3 Comments
 
LVL 14

Accepted Solution

by:
tusharkanvinde earned 500 total points
ID: 21794150
CREATE OR REPLACE FUNCTION temp(timestamp)
  RETURNS int2 AS
$BODY$

SELECT cast((case when extract(dow FROM  $1)>4 then 1 else extract(dow FROM  $1)+1 end) as int2) AS dow INTO dowCheck;

$BODY$
  LANGUAGE 'sql' VOLATILE;

The function parameter is a timestamp and not a string
0
 

Author Comment

by:tricl
ID: 21794512
Thanks for the fast reply, I have one more function which is suffering similar problems:
I have tried using a cast to convert the result which I guess is a double into in2 to find out if it is between 0 and 6 however I get the same $1 error.

Obviously I will accept your solution, but could you have a quick look at this other query within the points I am awarding?

Cheers
SELECT cast(extract(DOW FROM TIMESTAMP $1) as int2) AS doweek INTO dowCheck;
 

IF (dowCheck.doweek>0) AND (dowCheck.doweek<6) THEN

   RETURN TRUE;

ELSE

   RETURN FALSE;

END IF;

Open in new window

0
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 21795373
Glad it works for you. Try this

CREATE OR REPLACE FUNCTION doweek("timestamp")
  RETURNS bool AS
$BODY$
select case when  extract(dow from $1) between 1 and 5 then true else false end as doweek;
$BODY$
  LANGUAGE 'sql' VOLATILE;
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VASCO, PostgreSQL, IDENTIKEY, PostgreSQL Logs delete 4 661
Running PostgreSQL with 16k blocks instead of the standard 8k blocks? 4 88
sql table merge 11 150
DataBase Software 5 120
Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now