Re: [SQL] ANY for Array value check

[Jasen Betts]

On 2011-06-10, Emi Lu  wrote:
> Good morning,
>
> String array compare command, I forgot how to do it.
>
> E.g.,
> create table z_drop(id varchar[]);
> insert into z_drop values('{"a1", "a2", "b1", "b2", "b3"}');
>
> I'd like to do:
>
> select * from z_drop where id = any('a1', 'b1');

use the array overlap operator:


 select * from z_drop where   id  &&  ARRAY['a1'::varchar, 'b1'];


If you define the column as text[] instead of varchar you don't need
the ::varchar cast above.  there's no postgres reason to prefer
(unbounded) varchar to text. 

-- 
⚂⚃ 100% natural


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

[SQL] Subselects not allowed?

[Leif Biberg Kristensen]

Can anybody tell me why this doesn't work?

pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT 
MAX(source_id) FROM sources);
ERROR:  syntax error at or near "("
LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX...
 ^
pgslekt=> 

regards, Leif

-- 
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] Subselects not allowed?

[Guillaume Lelarge]

On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote:
> Can anybody tell me why this doesn't work?
> 

Because it's not supported. The START clause expects a value, not a
subquery.

> pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT 
> MAX(source_id) FROM sources);
> ERROR:  syntax error at or near "("
> LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX...
>  ^
> pgslekt=> 
> 
> regards, Leif
> 


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


-- 
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] Subselects not allowed?

[Tom Lane]

Guillaume Lelarge  writes:
> On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote:
>> Can anybody tell me why this doesn't work?
>> pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT 
>> MAX(source_id) FROM sources);
>> ERROR:  syntax error at or near "("

> Because it's not supported. The START clause expects a value, not a
> subquery.

More generally, there are no "utility" statements in PG that accept
non-constant expressions in their parameters.  (A utility statement is
anything other than SELECT, INSERT, UPDATE, DELETE.)

There's been occasional speculation about changing that, but it would
take a significant amount of work I think.

regards, tom lane

-- 
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] Subselects not allowed?

[Andreas Kretschmer]

Leif Biberg Kristensen  wrote:

> Can anybody tell me why this doesn't work?
> 
> pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT 
> MAX(source_id) FROM sources);
> ERROR:  syntax error at or near "("
> LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX...
>  ^
> pgslekt=> 
> 
> regards, Leif

You can't do that, but you can do this:

test=# select * from foo;
 i

 98
 99
(2 rows)

Time: 0,146 ms
test=*# \ds seq_foo;
No matching relations found.
test=*# do $$ declare m int; begin select into m max(i) from foo; execute 
'create sequence seq_foo start with ' || m; end; $$;
DO
Time: 1,115 ms
test=*# \ds seq_foo;
List of relations
 Schema |  Name   |   Type   |   Owner
+-+--+
 public | seq_foo | sequence | kretschmer
(1 row)

test=*# select * from seq_foo;
 sequence_name | last_value | start_value | increment_by |  max_value  
| min_value | cache_value | log_cnt | is_cycled | is_called
---++-+--+-+---+-+-+---+---
 seq_foo   | 99 |  99 |1 | 9223372036854775807 
| 1 |   1 |   1 | f | f
(1 row)



I'm using 9.1Beta, but it works since 9.0, see:
http://www.depesz.com/index.php/2009/11/01/waiting-for-8-5-do/#more-1535




Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
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] Subselects not allowed?

[Leif Biberg Kristensen]

On Saturday 11. June 2011 17.14.21 Tom Lane wrote:
> Guillaume Lelarge  writes:
> > On Sat, 2011-06-11 at 17:01 +0200, Leif Biberg Kristensen wrote:
> >> Can anybody tell me why this doesn't work?
> >> pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT
> >> MAX(source_id) FROM sources);
> >> ERROR:  syntax error at or near "("
> > 
> > Because it's not supported. The START clause expects a value, not a
> > subquery.
> 
> More generally, there are no "utility" statements in PG that accept
> non-constant expressions in their parameters.  (A utility statement is
> anything other than SELECT, INSERT, UPDATE, DELETE.)
> 
> There's been occasional speculation about changing that, but it would
> take a significant amount of work I think.

Thanks for the explanation, Tom.

Presumably this means that I can't automatically patch a live database to use 
a sequence unless I build a special function for the update. With 9.x I could 
use the new DO syntax, but that isn't yet deployed everywhere.

Unless there's a way around it?

regards, Leif

-- 
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] Subselects not allowed?

[Leif Biberg Kristensen]

On Saturday 11. June 2011 17.23.40 Andreas Kretschmer wrote:
> Leif Biberg Kristensen  wrote:
> > Can anybody tell me why this doesn't work?
> > 
> > pgslekt=> CREATE SEQUENCE sources_source_id_seq START WITH (SELECT
> > MAX(source_id) FROM sources);
> > ERROR:  syntax error at or near "("
> > LINE 1: CREATE SEQUENCE sources_source_id_seq START WITH (SELECT MAX...
> > 
> >  ^
> > 
> > pgslekt=>
> > 
> > regards, Leif
> 
> You can't do that, but you can do this:
> 
> test=# select * from foo;
>  i
> 
>  98
>  99
> (2 rows)
> 
> Time: 0,146 ms
> test=*# \ds seq_foo;
> No matching relations found.
> test=*# do $$ declare m int; begin select into m max(i) from foo; execute
> 'create sequence seq_foo start with ' || m; end; $$; DO
> Time: 1,115 ms
> test=*# \ds seq_foo;
> List of relations
>  Schema |  Name   |   Type   |   Owner
> +-+--+
>  public | seq_foo | sequence | kretschmer
> (1 row)
> 
> test=*# select * from seq_foo;
>  sequence_name | last_value | start_value | increment_by |  max_value  
>| min_value | cache_value | log_cnt | is_cycled | is_called
> ---++-+--+
> -+---+-+-+---+--- seq_foo  
> | 99 |  99 |1 | 9223372036854775807 | 
>1 |   1 |   1 | f | f (1 row)
> 
> 
> 
> I'm using 9.1Beta, but it works since 9.0, see:
> http://www.depesz.com/index.php/2009/11/01/waiting-for-8-5-do/#more-1535

Yes it works like a charm with 9.x, but it's not backwards compatible.

That looks like a problem waiting to happen. But of course I can create a one-
shot function and drop it afterwards.

regards, Leif

-- 
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] Subselects not allowed?

[Leif Biberg Kristensen]

I've written a blog post which I hope may be helpful to others in a similar 
situation:



Thanks to Guillaume Lelarge, Tom Lane, and Andreas Kretschmer. You guys are 
great.

regards, Leif
http://code.google.com/p/yggdrasil-genealogy/

-- 
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] Returning a set of dates

[C. Bensend]

> http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
>
>CREATE OR REPLACE FUNCTION public.next_bill_date(d date, period
>interval,
>   i interval)
>  RETURNS SETOF date
> AS $function$
> DECLARE
> max_date date;
> due_date date;
> BEGIN
> max_date := CURRENT_DATE + i;
> due_date := d;
> WHILE due_date + period <= max_date LOOP
> RETURN NEXT due_date; -- add d to the result set
> due_date := due_date + period;
> END LOOP;
> RETURN; -- exit function
> END;
> $function$ language plpgsql;
> testdb=# select next_bill_date('2011-06-11', '2 week', '3 month');
next_bill_date
> 
>  2011-06-11
>  2011-06-25
>  2011-07-09
>  2011-07-23
>  2011-08-06
>  2011-08-20

Almost, but not quite - the d parameter is a bill's "start date",
and the function shouldn't show dates in the past.  So, when the
above function is called with say '2011-06-01' as the beginning
date, the function will happily return '2011-06-01' in the result
set, even though it's in the past.

I've modified it a bit.  I renamed the function arguments to be a
bit more descriptive, did a little more math, and added an IF
statement to not return any dates in the past:


CREATE OR REPLACE FUNCTION public.next_bill_date(d date, frequency
   interval, daterange interval)
 RETURNS SETOF date
AS $function$
DECLARE
max_date date;
due_date date;
BEGIN
-- We need to add the epoch date and daterange together, to
-- get the "max_date" value.  However, this would cause us
-- to lose the last due date in the result set.  Add one more
-- frequency to it so we don't lose that.
max_date := CURRENT_DATE + frequency + daterange;
due_date := d;
WHILE due_date + frequency <= max_date LOOP
-- Don't include dates in the past - we only want future
-- due dates for bills.
IF due_date >= CURRENT_DATE
THEN
RETURN NEXT due_date;
END IF;
due_date := due_date + frequency;
END LOOP;
RETURN; -- exit function


This appears to work properly:

SELECT next_bill_date( '2011-06-01', '2 weeks', '3 months' );
 next_bill_date

 2011-06-15
 2011-06-29
 2011-07-13
 2011-07-27
 2011-08-10
 2011-08-24
 2011-09-07
(7 rows)

Thanks for all your help!  I'm not at all experienced with plpgsql,
so this was very much appreciated.  :)

Benny


-- 
"You were doing well until everyone died."
-- "God", Futurama





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