RE: [SQL] function that returns a set of records and integer(both of them)‏

Ok, i see........... well..... when i have to return a set of records i'll use sql and when i have to do stored procedure of functions i'll use plpsql in this case but you're telling me that it will change in the 8.4 version. won't it?
 
 


> Date: Sun, 13 Jul 2008 07:06:07 +0200
> From: pavel.stehule@gmail.com
> To: daniel_b_adrian@hotmail.com
> Subject: Re: [SQL] function that returns a set of records and integer(both of them)‏
> CC: pgsql-sql@postgresql.org
>
> Hello
>
> 2008/7/13 daniel blanco <daniel_b_adrian@hotmail.com>:
> > Ok, thanks pavel............. i think i'll try set of cursors..... by the
> > way in t-sql i did it as follows:
> > table users with a field called name
> >
> > create procedure list_user_by_name
> > @info_name varchar
> > as
> > declare @sw
> > begin
> > select @n=(select count(*) from users where name like
> > (@info_name+'%'))
> > if @sw>0
> > begin
> > select * where name like (@info_name+'%')
> > return 1
> > end
> > else
> > begin
> > return 0
> > end
> > end
> >
>
> do you thing return_status or global variables? It doesn't exist in
> PostreSQL. Your sample is typical example, what can be in stored
> procedure for MS and what would not be on Oracle like rdbms. This code
> is little bit ineffective. You have to call seq scan on users two
> times. Minimally this code on pg and oracle is:
>
> create function list_user_by_name(info_name)
> returns users as $$
> select * from users where name like $1 || '%'
> $$ language sql strict;
> -- you don't need logical info about returned set - this information
> is inside returned set, it is just empty or not empty.
>
> but it's better use directly well select than stored procedure in this
> case (8.4 will be different) - On Oracle like rdbsm SELECTs are not
> wrapped to procedures.
>
> p.s. postgresql doesn't support global (sessi on variables) - this
> topic was discussed in different thread this week
>
> Regards
> Pavel
>
> > as you see i do a select and a return at the same time when de sw variable
> > is > than 0. i was expecting to do a similiar function with plpgsql o sql in
> > postgresql
> >
> >
> >> Date: Sat, 12 Jul 2008 09:05:45 +0200
> >> From: pavel.stehule@gmail.com
> >> To: daniel_b_adrian@hotmail.com
> >> Subject: Re: [SQL] function that returns a set of records and integer(both
> >> of them)‏
> >> CC: pgsql-sql@postgresql.org
> >>
> >> 2008/7/11 daniel blanco <daniel_b_adrian@hotmail.com>:
> >> > Hi Everyone
> >> >
> >> > I would like to know if i can create a function that returns a set of
> >> > record
> >> > with the sql statemen t: select and a integer, i mean both of them,
> >> > because i
> >> > remenber that in sql server (transact sql) i can do that in a stored
> >> > procedure doing a select statement and a return of a integer in the same
> >> > procedure,
> >>
> >> when I worked with T-SQL this wasn't possible - stored procedure
> >> should return recordset or multirecord set or OUT variables. And it
> >> was solution. When I would returns some similar like you, I had to
> >> re turn two recordsets.
> >>
> >> i'm migrating to postgresql and i have stored procedures like
> >> > this in my sql server database, can i do that with postgresql?
> >>
> >> You cannot return integer and recordset from function now. There is
> >> one workaround, you can return set of cursors.
> >>
> >> Re gards
> >> Pavel Stehule
> >>
> >> >
> >> > Thanks.........
> >> > ________________________________
> >> > Get news, entertainment and everything you care about at Live.com. Check
> >> > it
> >> > out!
> >>
> >> --
> >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-sql
> >
> >
> > ________________________________
> > Connect to the next generation of MSN Messenger Get it now!

---

Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy! Try it!

Re: [PATCHES] variadic function support

2008/7/13 Jeff Davis <pgsql@j-davis.com>:
> On Sun, 2008-07-13 at 07:52 +0200, Pavel Stehule wrote:
>> you checked second or third variant? There are two variants still.
>
> Sorry for being unclear. Those comments regarded patch v2.2.1. The bug
> is in pg_function_is_visible().
it's bug, I'll fix it

>
> Additionally, I'd like to see support for calling variadic functions
> with no arguments. I mentioned that in my other email, but it applies to
> v2.2.1 as well.

for some cases, I can do less restriction.

>
> And we should come to a consensus quickly about the declaration style,
> e.g. "variadic int[]" or "variadic int".

ok
>
> Regards,
> Jeff Davis
>
>
thank you very much
Pavel Stehule

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

Re: [PATCHES] variadic function support

2008/7/13 Jeff Davis <pgsql@j-davis.com>:
> On Tue, 2008-06-24 at 17:10 +0200, Pavel Stehule wrote:
>> Hello
>>
>> this version implements syntax based on argmodes.
>>
>>
>> CREATE FUNCTION mleast(variadic numeric[]) RETURNS numeric AS $$
>> SELECT min($1[i])
>> FROM generate_subscripts($1,1) g(i);
>> $$ LANGUAGE SQL;
>>
>
> I don't have a strong opinion about whether the variadic argument is
> declared as an array or scalar, so I am posting my comments about this
> version of the patch as well.

ok

>
> This version also has a problem when declaring two functions "foo(int)"
> and "foo(variadic int[])". In this version, the declaration is allowed
> but the backend crashes when the function is called.
>

ok, I understand now

> The variable "transform_variadic" should have some kind of comment. It
> seems to be there to distinguish between when you're looking for a
> candidate function for a function call, and when you're looking for a
> candidate function for, e.g., CREATE FUNCTION. It's a little hard to
> follow, and is probably the cause for the aformentioned crash.
>
> Also, it doesn't seem to allow calls to a variadic function with zero
> arguments, e.g. "mleast()". I think this should be allowed.
>

It's not possible for all cases, because empty array have be typed
array still. But for non polymorphic variadic functions it's probably
possible - I would to solve this question later - and for now use
overloading etc

create function mleast() returns ..
create function mleast(variadic params anyarray) returns ...

> I suggest the following error message rewording:
> "variadic argument isn't an array" should be something like: "variadic
> argument must be an array".
>

I invite all you language suggestions. It's really important for me.

> Regards,
> Jeff Davis
>

my thanks
Pavel Stehule
>

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

Re: [PATCHES] variadic function support

On Sat, 2008-07-12 at 23:06 -0700, Jeff Davis wrote:
> I don't have a strong opinion about whether the variadic argument is
> declared as an array or scalar, so I am posting my comments about this
> version of the patch as well.
>
> This version also has a problem when declaring two functions "foo(int)"
> and "foo(variadic int[])". In this version, the declaration is allowed
> but the backend crashes when the function is called.
>
> The variable "transform_variadic" should have some kind of comment. It
> seems to be there to distinguish between when you're looking for a
> candidate function for a function call, and when you're looking for a
> candidate function for, e.g., CREATE FUNCTION. It's a little hard to
> follow, and is probably the cause for the aformentioned crash.
>
> Also, it doesn't seem to allow calls to a variadic function with zero
> arguments, e.g. "mleast()". I think this should be allowed.
>
> I suggest the following error message rewording:
> "variadic argument isn't an array" should be something like: "variadic
> argument must be an array".
>

To be clear, these comments apply to v2.0.0 of the patch.

Regards,
Jeff Davis

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

Re: [PATCHES] variadic function support

On Sun, 2008-07-13 at 07:52 +0200, Pavel Stehule wrote:
> you checked second or third variant? There are two variants still.

Sorry for being unclear. Those comments regarded patch v2.2.1. The bug
is in pg_function_is_visible().

Additionally, I'd like to see support for calling variadic functions
with no arguments. I mentioned that in my other email, but it applies to
v2.2.1 as well.

And we should come to a consensus quickly about the declaration style,
e.g. "variadic int[]" or "variadic int".

Regards,
Jeff Davis

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

Re: [PATCHES] variadic function support

On Tue, 2008-06-24 at 17:10 +0200, Pavel Stehule wrote:
> Hello
>
> this version implements syntax based on argmodes.
>
>
> CREATE FUNCTION mleast(variadic numeric[]) RETURNS numeric AS $$
> SELECT min($1[i])
> FROM generate_subscripts($1,1) g(i);
> $$ LANGUAGE SQL;
>

I don't have a strong opinion about whether the variadic argument is
declared as an array or scalar, so I am posting my comments about this
version of the patch as well.

This version also has a problem when declaring two functions "foo(int)"
and "foo(variadic int[])". In this version, the declaration is allowed
but the backend crashes when the function is called.

The variable "transform_variadic" should have some kind of comment. It
seems to be there to distinguish between when you're looking for a
candidate function for a function call, and when you're looking for a
candidate function for, e.g., CREATE FUNCTION. It's a little hard to
follow, and is probably the cause for the aformentioned crash.

Also, it doesn't seem to allow calls to a variadic function with zero
arguments, e.g. "mleast()". I think this should be allowed.

I suggest the following error message rewording:
"variadic argument isn't an array" should be something like: "variadic
argument must be an array".

Regards,
Jeff Davis

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

Re: [PATCHES] variadic function support

Hello

2008/7/13 Jeff Davis <pgsql@j-davis.com>:
> On Thu, 2008-06-26 at 17:03 +0200, Pavel Stehule wrote:
>> this is third variant with variadic argumen as scalar. But I still
>> strongly prefer second variant with conformance declared variadic
>> array with used array variable.
>

you checked second or third variant? There are two variants still.
Regards
Pavel Stehule

Please, Tom, can you choose one?

> This version allows you to declare two functions "foo(variadic numeric)"
> and "foo(numeric)", and then if you do a "\df foo" the backend crashes.
>
> Also, you didn't update an error string:
>
> "variadic argument isn't an array" should (in this version) be something
> like: "can't find array type for variadic parameter type %s"
>
> I suggest a slightly different wording for the following error messages:
>
> "aggregate function has variadic argument" -> "variadic parameters not
> supported for aggregate functions"
>
> and
>
> "variadic argument isn't last function's argument" -> "variadic
> parameter must be the last parameter to the function"
>
> Those are just suggested wordings.
>
> Regards,
> Jeff Davis
>
>

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

Re: [PATCHES] variadic function support

On Thu, 2008-06-26 at 17:03 +0200, Pavel Stehule wrote:
> this is third variant with variadic argumen as scalar. But I still
> strongly prefer second variant with conformance declared variadic
> array with used array variable.

This version allows you to declare two functions "foo(variadic numeric)"
and "foo(numeric)", and then if you do a "\df foo" the backend crashes.

Also, you didn't update an error string:

"variadic argument isn't an array" should (in this version) be something
like: "can't find array type for variadic parameter type %s"

I suggest a slightly different wording for the following error messages:

"aggregate function has variadic argument" -> "variadic parameters not
supported for aggregate functions"

and

"variadic argument isn't last function's argument" -> "variadic
parameter must be the last parameter to the function"

Those are just suggested wordings.

Regards,
Jeff Davis

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

Re: [GENERAL] Users functions library

Hello

safe it to pgfoundry http://pgfoundry.org/
regards
Pavel Stehule

2008/7/13 Alejandro D. Burne <alejandro.dburne@gmail.com>:
> 2008/7/12 Alejandro D. Burne <alejandro.dburne@gmail.com>:
>> Hi, I need to write a function that gives me a password string, no
>> just a numbers-characters string; something like people wrote in php,
>> its based on determined syllables and numbers.
>> I think it be useful to other people, is there a site where one can
>> post it and share with other postgres users?
>>
>> Alejandro
>>
>
> Sorry, the function:
>
> CREATE OR REPLACE FUNCTION gen_password(plenght smallint)
> RETURNS bpchar AS
> $BODY$
>
> DECLARE
> lValid_Consonant bpchar DEFAULT 'BCDFGHJKMNPRSTV';
> lValid_Vowel bpchar DEFAULT 'AEIOUY';
> lValid_Numbers bpchar DEFAULT '23456789';
>
> lConsonant_Length smallint DEFAULT char_length(lValid_Consonant);
> lVowel_Length smallint DEFAULT char_length(lValid_Vowel);
> lNumbers_Length smallint DEFAULT char_length(lValid_Numbers);
>
> lPassword bpchar DEFAULT '';
>
> BEGIN
> LOOP
> IF ROUND(RANDOM()*3)<>1 THEN
> lPassword:=lPassword||SUBSTRING(lValid_Consonant FROM
> (ROUND(RANDOM()*(lConsonant_Length-1))+1)::integer FOR 1)||
> SUBSTRING(lValid_Vowel FROM
> (ROUND(RANDOM()*(lVowel_Length-1))+1)::integer FOR 1);
> IF ROUND(RANDOM()*2)<>1 THEN
> lPassword:=lPassword||SUBSTRING(lValid_Consonant FROM
> (ROUND(RANDOM()*(lConsonant_Length-1))+1)::integer FOR 1);
> END IF;
> ELSE
> lPassword:=lPassword||SUBSTRING(lValid_Numbers FROM
> (ROUND(RANDOM()*(lNumbers_Length-1))+1)::integer FOR 1);
> END IF;
> IF char_length(lPassword) >= plenght THEN
> EXIT;
> END IF;
> END LOOP;
>
> RETURN SUBSTRING(lPassword FROM 1 FOR plenght);
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

Re: [SQL] function that returns a set of records and integer(both of them)‏

Hello

2008/7/13 daniel blanco <daniel_b_adrian@hotmail.com>:
> Ok, thanks pavel............. i think i'll try set of cursors..... by the
> way in t-sql i did it as follows:
> table users with a field called name
>
> create procedure list_user_by_name
> @info_name varchar
> as
> declare @sw
> begin
> select @n=(select count(*) from users where name like
> (@info_name+'%'))
> if @sw>0
> begin
> select * where name like (@info_name+'%')
> return 1
> end
> else
> begin
> return 0
> end
> end
>

do you thing return_status or global variables? It doesn't exist in
PostreSQL. Your sample is typical example, what can be in stored
procedure for MS and what would not be on Oracle like rdbms. This code
is little bit ineffective. You have to call seq scan on users two
times. Minimally this code on pg and oracle is:

create function list_user_by_name(info_name)
returns users as $$
select * from users where name like $1 || '%'
$$ language sql strict;
-- you don't need logical info about returned set - this information
is inside returned set, it is just empty or not empty.

but it's better use directly well select than stored procedure in this
case (8.4 will be different) - On Oracle like rdbsm SELECTs are not
wrapped to procedures.

p.s. postgresql doesn't support global (session variables) - this
topic was discussed in different thread this week

Regards
Pavel

> as you see i do a select and a return at the same time when de sw variable
> is > than 0. i was expecting to do a similiar function with plpgsql o sql in
> postgresql
>
>
>> Date: Sat, 12 Jul 2008 09:05:45 +0200
>> From: pavel.stehule@gmail.com
>> To: daniel_b_adrian@hotmail.com
>> Subject: Re: [SQL] function that returns a set of records and integer(both
>> of them)‏
>> CC: pgsql-sql@postgresql.org
>>
>> 2008/7/11 daniel blanco <daniel_b_adrian@hotmail.com>:
>> > Hi Everyone
>> >
>> > I would like to know if i can create a function that returns a set of
>> > record
>> > with the sql statement: select and a integer, i mean both of them,
>> > because i
>> > remenber that in sql server (transact sql) i can do that in a stored
>> > procedure doing a select statement and a return of a integer in the same
>> > procedure,
>>
>> when I worked with T-SQL this wasn't possible - stored procedure
>> should return recordset or multirecord set or OUT variables. And it
>> was solution. When I would returns some similar like you, I had to
>> re turn two recordsets.
>>
>> i'm migrating to postgresql and i have stored procedures like
>> > this in my sql server database, can i do that with postgresql?
>>
>> You cannot return integer and recordset from function now. There is
>> one workaround, you can return set of cursors.
>>
>> Regards
>> Pavel Stehule
>>
>> >
>> > Thanks.........
>> > ________________________________
>> > Get news, entertainment and everything you care about at Live.com. Check
>> > it
>> > out!
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>
>
> ________________________________
> Connect to the next generation of MSN Messenger Get it now!

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

Re: [HACKERS] PATCH: CITEXT 2.0 v3

On Jul 12, 2008, at 14:50, David E. Wheeler wrote:

>> * An explicit comment explaining that you're piggybacking on the I/O
>> functions (and some others) for "text" wouldn't be out of place.
>
> I've added SQL comments. Were you talking about a COMMENT?
>
>> * Lose the GRANT EXECUTEs on the I/O functions; they're redundant.
>> (If you needed them, you'd need them on a lot more than these two.)
>> I'd be inclined to lose the COMMENTs on the functions too; again
>> these are about the least useful ones to comment on out of the
>> whole module.
>
> I wondered about that; those were copied from CITEXT 1. I've removed
> all GRANTs and COMMENTs.
>
>> * You should provide binary I/O (send/receive) functions, if you want
>> this to be an industrial-strength module. It's easy since you can
>> piggyback on text's.
>
> I'm confused. Is that not what the citextin and citextout functions
> are?
>
>> * The type declaration needs to say storage = extended, else the type
>> won't be toastable.
>
> Ah, good, thanks.
>
>> * The cast from bpchar to citext cannot be WITHOUT FUNCTION;
>> it needs to invoke rtrim1. Compare the bpchar to text cast.
>
> Where do I find that? I have trouble finding the SQL that creates
> the core types. :-(

Duh, you just told me. Added, thanks.

>> * <= is surely not its own commutator.
>
> Changed to >=.
>
>> You might try running the
>> opr_sanity regression test on this module to see if it finds any
>> other silliness. (Procedure: insert the citext definition script
>> into the serial_schedule list just ahead of opr_sanity, run tests,
>> make sure you understand the reason for any diffs in the opr_sanity
>> result. There will be at least one from the uses of text-related
>> functions for citext.)
>
> Thanks. Added to my list.
>
>> * I think you can and should drop all of the "size" functions and
>> a lot of the "miscellaneous" functions: anyplace where the implicit
>> coercion to text would serve, you don't need a piggyback function,
>> and introducing one just creates risks of
>> can't-resolve-ambiguous-function failures. The overloaded
>> miscellaneous
>> functions are only justifiable to the extent that it's important to
>> preserve "citext-ness" of the result of a function, which seems at
>> best dubious for many of these. It is likewise pretty pointless
>> AFAICS
>> to introduce regex functions taking citext pattern arguments.
>
> I added most of those as I wrote tests and they failed to find the
> functions. Once I added the functions, they worked. But I'll do an
> audit to make sure that I didn't inadvertantly leave in any unneeded
> ones (I'm happy to have less code :-)).

Of the size functions, I was able to remove only this one and keep all
of my pgTAP tests passing:

CREATE FUNCTION textlen(citext)
RETURNS int4 AS 'textlen'
LANGUAGE 'internal' IMMUTABLE STRICT;

When I deleted any of the others, I got errors like this:

psql:sql/citext.sql:865: ERROR: function length(citext) is not unique
LINE 1: SELECT is( length( name ), length(name::text), 'length("' ||...
^
HINT: Could not choose a best candidate function. You might need to
add explicit type casts.

I think you can see now why I wrote the tests: I wanted to ensure that
CITEXT really does work (almost) just like TEXT.

I was able to eliminate *all* of the miscellaneous functions, but the
upper() and lower() functions now return TEXT instead of CITEXT, which
I don't think is exactly what we want, is it? For now, I'e left
upper() and lower() in. It just seems like more expected functionality.

>> * Don't use the OPERATOR() notation when you don't need to.
>> It's just clutter.
>
> Sorry, don't know what you're referring to here. CREATE OPERATOR
> appears to require parens…

Best,

David

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

Re: [GENERAL] Users functions library

2008/7/12 Alejandro D. Burne <alejandro.dburne@gmail.com>:
> Hi, I need to write a function that gives me a password string, no
> just a numbers-characters string; something like people wrote in php,
> its based on determined syllables and numbers.
> I think it be useful to other people, is there a site where one can
> post it and share with other postgres users?
>
> Alejandro
>

Sorry, the function:

CREATE OR REPLACE FUNCTION gen_password(plenght smallint)
RETURNS bpchar AS
$BODY$

DECLARE
lValid_Consonant bpchar DEFAULT 'BCDFGHJKMNPRSTV';
lValid_Vowel bpchar DEFAULT 'AEIOUY';
lValid_Numbers bpchar DEFAULT '23456789';

lConsonant_Length smallint DEFAULT char_length(lValid_Consonant);
lVowel_Length smallint DEFAULT char_length(lValid_Vowel);
lNumbers_Length smallint DEFAULT char_length(lValid_Numbers);

lPassword bpchar DEFAULT '';

BEGIN
LOOP
IF ROUND(RANDOM()*3)<>1 THEN
lPassword:=lPassword||SUBSTRING(lValid_Consonant FROM
(ROUND(RANDOM()*(lConsonant_Length-1))+1)::integer FOR 1)||
SUBSTRING(lValid_Vowel FROM
(ROUND(RANDOM()*(lVowel_Length-1))+1)::integer FOR 1);
IF ROUND(RANDOM()*2)<>1 THEN
lPassword:=lPassword||SUBSTRING(lValid_Consonant FROM
(ROUND(RANDOM()*(lConsonant_Length-1))+1)::integer FOR 1);
END IF;
ELSE
lPassword:=lPassword||SUBSTRING(lValid_Numbers FROM
(ROUND(RANDOM()*(lNumbers_Length-1))+1)::integer FOR 1);
END IF;
IF char_length(lPassword) >= plenght THEN
EXIT;
END IF;
END LOOP;

RETURN SUBSTRING(lPassword FROM 1 FOR plenght);
END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;

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

Re: [HACKERS] PATCH: CITEXT 2.0 v3

On Jul 12, 2008, at 15:13, David E. Wheeler wrote:

>> Sadly, I think you have to give up
>> attempts to check the interesting multibyte cases and confine
>> yourself
>> to tests using ASCII strings.
>
> Grr. Kind of defeats the purpose. Is there no infrastructure for
> testing multibyte functionality? Are test database clusters all
> built using SQL_ASCII and the C locale?

I just tried to take your modified tests and add multibyte tests that
run only on OS X with en_US.UTF-8. They worked like this:

CREATE OR REPLACE FUNCTION test_multibyte ()
RETURNS BOOLEAN AS $$
SELECT version() ~ 'apple-darwin'
AND (select setting from pg_settings where name = 'lc_collate')
= 'en_US.UTF-8';
$$ LANGUAGE SQL IMMUTABLE;

SELECT 'À'::citext = 'À'::citext WHERE test_multibyte() = true;
SELECT 'À'::citext = 'à'::citext WHERE test_multibyte() = true;

But then I realized that this would change the expected output
depending on the platform, and thus make the tests fail. This is one
reason why the inflexibility of the existing regression test model is
a drag: it limits you to testing only what works everywhere!

Grrr.

I'll remove all the multibyte character tests, but I have to say that,
as a result, the CITEXT 1 module would likely pass all such tests,
but it still isn't locale-aware. How can one add regressions to ensure
that something truly is locale-aware?

Thanks,

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

RE: [SQL] function that returns a set of records and integer(both of them)‏

Ok, thanks pavel............. i think i'll try set of cursors..... by the way in t-sql i did it as follows:
table users with a field called name
 
create procedure list_user_by_name
@info_name varchar
as
declare @sw
begin
         select @n=(select count(*) from users where name like (@info_name+'%'))
         if @sw>0
         begin
                   select * where name like (@info_name+'%')
                   return 1
         end
         else
         begin
                   return 0
         end
end
 
as you see i do a select and a return at the same time when de sw variable is > than 0. i was expecting to do a similiar function with plpgsql o sql in postgresql


> Date: Sat, 12 Jul 2008 09:05:45 +0200
> From: pavel.stehule@gmail.com
> To: daniel_b_adrian@hotmail.com
> Subject: Re: [SQL] function that returns a set of records and integer(both of them)‏
> CC: pgsql-sql@postgresql.org
>
> 2008/7/11 daniel blanco <daniel_b_adrian@hotmail.com>:
> > Hi Everyone
> >
> > I would like to know if i can create a function that returns a set of record
> > with the sql statement: select and a integer, i mean both of them, because i
> > remenber that in sql server (transact sql) i can do that in a stored
> > procedure doing a select statement and a return of a integer in the same
> > procedure,
>
> when I worked with T-SQL this wasn't possible - stored procedure
> should return recordset or multirecord set or OUT variables. And it
> was solution. When I would returns some similar like you, I had to
> re turn two recordsets.
>
> i'm migrating to postgresql and i have stored procedures like
> > this in my sql server database, can i do that with postgresql?
>
> You cannot return integer and recordset from function now. There is
> one workaround, you can return set of cursors.
>
> Regards
> Pavel Stehule
>
> >
> > Thanks.........
> > ________________________________
> > Get news, entertainment and everything you care about at Live.com. Check it
> > out!
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

---

Connect to the next generation of MSN Messenger  Get it now!

[GENERAL] Users functions library

Hi, I need to write a function that gives me a password string, no
just a numbers-characters string; something like people wrote in php,
its based on determined syllables and numbers.
I think it be useful to other people, is there a site where one can
post it and share with other postgres users?

Alejandro

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

Re: [pgsql-es-ayuda] Server doesn`t listen

chequea el firewall de paso
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [SQL] Copyright and Paper walls

Steve writes:
> I don't want to pile more wood on the fire, but I think I can see both
> sides to this. I believe this is not so much copyright violation concern,
> but if the Pg team releases some cool feature relating to rollbacks
> down-the-road that is vaguely similar to Oracle's system, reducing the
> amount of discussion about Oracle's features on this list would reduce
> Oracle's ability to claim that the feature was a direct appropriation.

So what if it is direct appropriation? Either it is patented, in which
case you infringe whether you looked at their docs or not, or it isn't, in
which case they have no grounds for action. There is nothing wrong with
discussing Oracle's features or even deliberately duplicating them.
--
John Hasler
john@dhh.gt.org
Elmwood, WI USA

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

Re: [HACKERS] PATCH: CITEXT 2.0 v3

On Jul 12, 2008, at 14:57, Tom Lane wrote:

> There was some discussion earlier about whether the proposed
> regression
> tests for citext are suitable for use in contrib or not. After
> playing
> with them for awhile, I have to come down very firmly on the side of
> "not". I have these gripes:

You're nothing if not thorough, Tom.

> 1. The style is gratuitously different from every other regression
> test
> in the system. This is not a good thing. If it were an amazingly
> better way to do things, then maybe, but as far as I can tell the
> style
> pgTAP forces on you is really pretty darn poorly suited for SQL tests.
> You have to contort what could naturally be expressed in SQL as a
> table
> result into a scalar. Plus it's redundant with the expected-output
> file.

Sure. I wasn't aware of the existing regression test methodology when
I wrote pgTAP and those tests. I'm fine to change them and use pgTAP
for testing my app code, rather than PostgreSQL itself.

> 2. It's ridiculously slow; at least a factor of ten slower than doing
> equivalent tests directly in SQL. This is a very bad thing. Speed of
> regression tests matters a lot to those of us who run them a dozen
> times
> per day --- and I do not wish to discourage any developers who don't
> work that way from learning better habits ;-)

Hrm. I'm wonder why it's so slow? The test functions don't really do a
lot. Anyway, I agree that they should perform well.

> Because of #1 and #2 I find the use of pgTAP to be a nonstarter.
> I have a couple of gripes about the substance of the tests as well:
>
> 3. What you are mostly testing is not the behavior of citext itself,
> but the behavior of the underlying strcoll function. This is pretty
> much doomed to failure, first because the regression tests are
> intended
> to work in multiple locales (and we are *not* giving that up for
> citext), and second because the behavior of strcoll isn't all that
> portable across platforms even given allegedly similar locale settings
> (as we already found out yesterday).

Yes, I *just* ran the tests on Ubuntu and opened my mail to ask about
the bizarre differences when I saw this message. Thanks for answering
my question before I asked it. :-)

> Sadly, I think you have to give up
> attempts to check the interesting multibyte cases and confine yourself
> to tests using ASCII strings.

Grr. Kind of defeats the purpose. Is there no infrastructure for
testing multibyte functionality? Are test database clusters all built
using SQL_ASCII and the C locale?

> 4. A lot of the later test cases are equally uselessly testing whether
> piggybacking over text functions works. The odds of ever finding
> anything with those tests are not distinguishable from zero (unless
> the
> underlying text function is busted, which is not your responsibility
> to
> test). So I don't see any point in putting them into the standard
> regression package. (What maybe *would* be useful to test, but you
> didn't, is whether the result of a function is considered citext
> rather
> than text.)

Well, I was doing test-driven development: I wrote the tests to ensure
that I had added the functions for CITEXT properly, and when they
passed, I could move on. As a unit tester, it'd feel weird for me to
drop these. I'm not testing the underlying functions; I'm making sure
that they work properly with CITEXT.

> I suggest something more like the attached as a suitable regression
> test. I got bored about halfway through and started to skim, so there
> might be a few tests toward the end of the original set that would be
> worth transposing into this one, but nothing jumped out at me.

Thanks! I'll work this in.

Best,

David

PS: I confirmed late yesterday that the memory leak I saw was with my
version for 8.3, where I had my own str_tolower(). It clearly has a
leak that I'll have to fix, but it has no bearing on the contribution
to 8.4, which has no such leak. Thanks for running the test yourself
to confirm.

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

Re: [HACKERS] PATCH: CITEXT 2.0 v3

There was some discussion earlier about whether the proposed regression
tests for citext are suitable for use in contrib or not. After playing
with them for awhile, I have to come down very firmly on the side of
"not". I have these gripes:

1. The style is gratuitously different from every other regression test
in the system. This is not a good thing. If it were an amazingly
better way to do things, then maybe, but as far as I can tell the style
pgTAP forces on you is really pretty darn poorly suited for SQL tests.
You have to contort what could naturally be expressed in SQL as a table
result into a scalar. Plus it's redundant with the expected-output file.

2. It's ridiculously slow; at least a factor of ten slower than doing
equivalent tests directly in SQL. This is a very bad thing. Speed of
regression tests matters a lot to those of us who run them a dozen times
per day --- and I do not wish to discourage any developers who don't
work that way from learning better habits ;-)

Because of #1 and #2 I find the use of pgTAP to be a nonstarter.
I have a couple of gripes about the substance of the tests as well:

3. What you are mostly testing is not the behavior of citext itself,
but the behavior of the underlying strcoll function. This is pretty
much doomed to failure, first because the regression tests are intended
to work in multiple locales (and we are *not* giving that up for
citext), and second because the behavior of strcoll isn't all that
portable across platforms even given allegedly similar locale settings
(as we already found out yesterday). Sadly, I think you have to give up
attempts to check the interesting multibyte cases and confine yourself
to tests using ASCII strings.

4. A lot of the later test cases are equally uselessly testing whether
piggybacking over text functions works. The odds of ever finding
anything with those tests are not distinguishable from zero (unless the
underlying text function is busted, which is not your responsibility to
test). So I don't see any point in putting them into the standard
regression package. (What maybe *would* be useful to test, but you
didn't, is whether the result of a function is considered citext rather
than text.)

I suggest something more like the attached as a suitable regression
test. I got bored about halfway through and started to skim, so there
might be a few tests toward the end of the original set that would be
worth transposing into this one, but nothing jumped out at me.

regards, tom lane

Re: [HACKERS] PATCH: CITEXT 2.0 v3

On Jul 12, 2008, at 12:17, Tom Lane wrote:

> BTW, I looked at the SQL file (citext.sql.in) a bit. Some comments:

Thanks a million for these, Tom. I greatly appreciate it.

> * An explicit comment explaining that you're piggybacking on the I/O
> functions (and some others) for "text" wouldn't be out of place.

I've added SQL comments. Were you talking about a COMMENT?

> * Lose the GRANT EXECUTEs on the I/O functions; they're redundant.
> (If you needed them, you'd need them on a lot more than these two.)
> I'd be inclined to lose the COMMENTs on the functions too; again
> these are about the least useful ones to comment on out of the
> whole module.

I wondered about that; those were copied from CITEXT 1. I've removed
all GRANTs and COMMENTs.

> * You should provide binary I/O (send/receive) functions, if you want
> this to be an industrial-strength module. It's easy since you can
> piggyback on text's.

I'm confused. Is that not what the citextin and citextout functions are?

> * The type declaration needs to say storage = extended, else the type
> won't be toastable.

Ah, good, thanks.

> * The cast from bpchar to citext cannot be WITHOUT FUNCTION;
> it needs to invoke rtrim1. Compare the bpchar to text cast.

Where do I find that? I have trouble finding the SQL that creates the
core types. :-(

> * <= is surely not its own commutator.

Changed to >=.

> You might try running the
> opr_sanity regression test on this module to see if it finds any
> other silliness. (Procedure: insert the citext definition script
> into the serial_schedule list just ahead of opr_sanity, run tests,
> make sure you understand the reason for any diffs in the opr_sanity
> result. There will be at least one from the uses of text-related
> functions for citext.)

Thanks. Added to my list.

> * I think you can and should drop all of the "size" functions and
> a lot of the "miscellaneous" functions: anyplace where the implicit
> coercion to text would serve, you don't need a piggyback function,
> and introducing one just creates risks of
> can't-resolve-ambiguous-function failures. The overloaded
> miscellaneous
> functions are only justifiable to the extent that it's important to
> preserve "citext-ness" of the result of a function, which seems at
> best dubious for many of these. It is likewise pretty pointless
> AFAICS
> to introduce regex functions taking citext pattern arguments.

I added most of those as I wrote tests and they failed to find the
functions. Once I added the functions, they worked. But I'll do an
audit to make sure that I didn't inadvertantly leave in any unneeded
ones (I'm happy to have less code :-)).

> * Don't use the OPERATOR() notation when you don't need to.
> It's just clutter.

Sorry, don't know what you're referring to here. CREATE OPERATOR
appears to require parens…

Thanks for the great feedback! I'll work on getting things all
straightened out and a new patch in tonight.

Best,

David

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

Re: [HACKERS] gsoc, text search selectivity and dllist enhancments

OK, here's the (hopefully final) version of the typanalyze function for
tsvectors. It applies to HEAD and passes regression tests.

I now plan to move towards a selectivity function that'll use the
gathered statistics.

Thanks to everyone for their reviews and comments,

Cheers,
Jan

--
Jan Urbanski
GPG key ID: E583D7D2

ouden estin

Re: [HACKERS] Extending grant insert on tables to sequences

Abhijit Menon-Sen <ams@oryx.com> writes:
> But I notice that nobody else has commented on whether they want this
> feature or not. Does anyone particularly dislike the idea?

I think it's probably reasonable as long as we keep the implicitly
granted rights as narrow as possible. INSERT on the parent table
would normally be hard to use correctly if you can't nextval() the
sequence, so automatically allowing nextval() seems pretty reasonable.
I think the case for granting anything more than that is weak ---
even without considering backwards-compatibility arguments.

A fairly important practical problem is whether this will keep pg_dump
from correctly reproducing the state of a database. Assume that someone
did revoke the implicitly-granted rights on the sequence --- would a
dump and reload correctly preserve that state? It'd depend on the order
in which pg_dump issued the GRANTs, and I'm not at all sure pg_dump
could be relied on to get that right. (Even if we fixed it to account
for the issue today, what of older dump scripts?)

Another issue is the interaction with the planned column-level GRANT
feature. AFAICS, the obvious-sounding rule that usage of the sequence
should be granted consequent to granting INSERT on the owning column
would be exactly backwards. It's when you have *not* got INSERT on
that column that you *must* rely on the default for it, and hence you'd
better have the ability to do nextval() or your alleged insert
privileges on other columns are worthless. So it seems that sequence
usage should be granted if any column INSERT is granted, and revoked
only when all column INSERT privileges are revoked --- and that latter
rule is going to be hard to implement with this type of patch, because
it doesn't know what column privileges are going to remain.

I thought for a bit about abandoning the proposed implementation and
instead having nextval/currval check at runtime: IOW, if the check for
ACL_USAGE on the sequence fails, look to see if the sequence is "owned"
and if so look to see if the user has ACL_INSERT on the parent table.
(This seems a bit slow but maybe it wouldn't be a problem, or maybe we
could arrange to cache the lookup results.) This would avoid the
"action at a distance" behavior in GRANT and thereby cure both of
the problems mentioned above. However, it would mean that it'd be
impossible to grant INSERT without effectively granting sequence USAGE
--- revoking USAGE on the sequence wouldn't stop anything. Plus, \z on
the sequence would fail to tell you about those implicitly held rights.
So I'm not sure I like this way any better.

regards, tom lane

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

[ADMIN] How to setup tablespace and table partitioning in a template?

************************************************************************
* Do not Cc: me, because I READ THIS LIST, if I write here *
* Keine Cc: am mich, ich LESE DIESE LISTE wenn ich hier schreibe *
************************************************************************

Hello,

Our PostgreSQL server is using two SCSI Raid-1 controllers (each two
channels) and I have 5 sets per channel (each two HDD in Raid-1 with one
HotFix). In summary we have currently 15 Sets.

Sometimes we have the need to duplicate servers and I tried to use the
database scheme dump to install a new server...

This dos not work. ALL databases and tables are created on a singel
Raid-1 (the first one) and tablespace is ignored.

Can anyone post an example how to do this, so I can edit my pgSQL dump
sheme to correct this issue?

Note: It is not very funny to setup each time 15 Raid-1 sets by hand
since the PostgreSQL has now reached 1400 GByte and we will
use the second channel of the second controller to attach
again 15 new drives which then support up to 2.9 TByte. The
drives have all 146 GByte.

Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant

--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
+49/177/9351947 50, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)