Re: [SQL] SQL syntax rowcount value as an extra column in the result set

2010-03-26 Thread Thomas Kellerer 

Jayadevan M, 26.03.2010 07:56:

Thank you for setting that right. Apologies for not checking version.


The orginal poster stated that he is using 8.4, so that solution will work for 
him.

Thomas


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Plpgsql: Iterating through a string of parameters

2010-03-26 Thread Jasen Betts 
On 2010-03-25, Leif Biberg Kristensen  wrote:
> I'm struggling with how to make plpgsql iterate through a list of numbers 
> input as a text string, eg. "1438 2656 973 4208". I figure that I can use the 
> regexp_split_to_array() function to make an array of the string, but can I 
> iterate through an array with eg. a FOR loop?

using regex_split_to_table with for is easier

 for x in regex_split_to_table() do
 
But if you must you can use split to array and then use the array measuring
functions to determine the limit for a counted for loop.



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Week to date function

2010-03-26 Thread Ireneusz Pluta 

Sergey Konoplev pisze:

On 25 March 2010 12:25, Ireneusz Pluta  wrote:
  

Hello,

is there any standard function, or a concise solution based on set of them,
returning a set of dates included in a week of given year and week number?
I ended up with creating my own function as in the example below, but I am
curious if I am not opening an open door.



Try to think of something like this?

SELECT
date_trunc('week', '2010-01-01'::date) +
'12 week'::interval +
(d::text||' day')::interval
FROM generate_series(0, 6) AS d;

  

Yes, much smarter.
However, would give the same results on (year=2009, week=53) and 
(year=2010, week=1). In fact, 2009 did not have week 53.

I wrapped it into a function with additional isoyear check and now seems OK.

Thanks


CREATE OR REPLACE FUNCTION your_week2date(double precision, double 
precision) RETURNS SETOF date

AS
$_$
SELECT  day
FROM (
   SELECT  (
   date_trunc('week', ($1::text||'-01-01')::date)
   + ($2::text||' week')::interval
   + ( d::text||'  day')::interval
   )::date  AS  day
   FROMgenerate_series(0, 6) AS d
)   alias
WHERE   to_char(day, 'IYYY')::integer = $1
ORDER
BY  1
$_$
LANGUAGE SQL
IMMUTABLE
;
SELECT week2date1(date_part('year', now()), date_part('week', now()));
week2date1

2010-03-22
2010-03-23
2010-03-24
2010-03-25
2010-03-26
2010-03-27
2010-03-28
(7 rows)

SELECT your_week2date(2009, 52) ;
your_week2date

2009-12-28
2009-12-29
2009-12-30
2009-12-31
2010-01-01
2010-01-02
2010-01-03
(7 rows)

SELECT your_week2date(2009, 53) ;
your_week2date

(0 rows)

SELECT your_week2date(2010,  1) ;
your_week2date

2010-01-04
2010-01-05
2010-01-06
2010-01-07
2010-01-08
2010-01-09
2010-01-10
(7 rows)


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Week to date function

2010-03-26 Thread Sergey Konoplev 
> CREATE OR REPLACE FUNCTION your_week2date(double precision, double
> precision) RETURNS SETOF date
> AS
> $_$
> SELECT  day
> FROM (
>   SELECT  (
>               date_trunc('week', ($1::text||'-01-01')::date)
>               + ($2::text||' week')::interval
>               + ( d::text||'  day')::interval
>           )::date  AS  day
>   FROM    generate_series(0, 6) AS d
> )   alias
> WHERE   to_char(day, 'IYYY')::integer = $1
> ORDER
> BY      1
> $_$
> LANGUAGE SQL
> IMMUTABLE

I think it is better to use date_part('year', day) instead of
to_char(...). And may be it is worth to do raise exception when
incorrect week specified.

-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Week to date function

2010-03-26 Thread Ireneusz Pluta 

Sergey Konoplev pisze:

CREATE OR REPLACE FUNCTION your_week2date(double precision, double
precision) RETURNS SETOF date
AS
$_$
SELECT  day
FROM (
  SELECT  (
  date_trunc('week', ($1::text||'-01-01')::date)
  + ($2::text||' week')::interval
  + ( d::text||'  day')::interval
  )::date  AS  day
  FROMgenerate_series(0, 6) AS d
)   alias
WHERE   to_char(day, 'IYYY')::integer = $1
ORDER
BY  1
$_$
LANGUAGE SQL
IMMUTABLE



I think it is better to use date_part('year', day) instead of
to_char(...).

this might cut first or last week in the year, like this:


SELECT your_week2date(2009, 52) ;
your_week2date

2009-12-28
2009-12-29
2009-12-30
2009-12-31
2010-01-01   cut
2010-01-02   cut
2010-01-03   cut
(7 rows)

which is not what I want when playing with isoweeks.

 And may be it is worth to do raise exception when
incorrect week specified.
  
but, maybe, controlled by an additonal parameter saying if one prefers 
to have exception or rather an empty resultset



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql