Re: [GENERAL] Obtain boolean value of expression in PLPGSQL

[Richard Huxton]

On Tuesday 04 November 2003 10:06, Max Speransky wrote:
> Hello
>
> My task is to validate expression and get value of it in boolean variable.
> I try to do following:

> Ret := 3 || Op || $1;
> RETURN Ret;

> bill=# select get_value(3);
> WARNING:  Error occurred while executing PL/pgSQL function get_value
> WARNING:  line 6 at assignment
> ERROR:  Bad boolean external representation '3=3'

The problem is that your expression is being evaluated as a string. In the 
absence of an eval() function, you probably should do something like:

my_stmt := ''SELECT '' || 3 || Op || $1;
EXECUTE my_stmt;
FOR EACH ...

So - build a simple query, execute it and read the result. Should do what you 
want.

-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] SELECT question

[Richard Huxton]

On Tuesday 04 November 2003 10:54, Alex wrote:
> Hi,
>
> I have a bit string , 7 bits, every bit representing a day of the week.
> e.g. 1110011.
> Is there and easy way where I can translate/format that string in a query.
> I want to give the string back with a '-' for every 0 and the first char
> of the Day for every '1'.
> example 1100111 = SM--TFS.

You probably want a small function in plpgsql - see the procedural languages 
section of the manual for details. You might want to check the cookbook at  
http://techdocs.postgresql.org/ and see if there's similar code you can use 
as inspiration.

-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string

[Andrew Sullivan]

On Tue, Nov 04, 2003 at 11:21:35AM +, Rob Fielding wrote:
> 
> 
> Hi,
> 
> We're currently experiencing a problem where SQL statements are failing 
> when entring a '' for not not-null integer columns:

Yes.  This behaviour was made more compliant in 7.3.  It's in the
release notes.

> The above example in just one case where 'aid' can accept a null value. 

That's not a null.  It's a zero-length string.

> The use of quotes around all values was established as IIRC pg7.2 
> wouldn't accept statements without them. The use of this convention is 
> extensive.

You could probably put in a rewrite rule to convert '' to NULL and
allow nulls on the column.  It's the only suggestion I can think of,
short of going back to 7.2.

A

-- 

Andrew Sullivan 204-4141 Yonge Street
Afilias CanadaToronto, Ontario Canada
<[EMAIL PROTECTED]>  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string

[Markus Wollny]

> -Ursprüngliche Nachricht-
> Von: Andrew Sullivan [mailto:[EMAIL PROTECTED]
> Gesendet: Dienstag, 4. November 2003 12:32
> An: [EMAIL PROTECTED]
> Betreff: Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string
> 
> 
> On Tue, Nov 04, 2003 at 11:21:35AM +, Rob Fielding wrote:
> > We're currently experiencing a problem where SQL statements 
> are failing 
> > when entring a '' for not not-null integer columns:
> 
> Yes.  This behaviour was made more compliant in 7.3.  It's in the
> release notes.
[snip]
> You could probably put in a rewrite rule to convert '' to NULL and
> allow nulls on the column.  It's the only suggestion I can think of,
> short of going back to 7.2.

No, there's actually another option. Bruce posted a patch for reverting
to 7.2-behaviour (well, sort of...); I currently cannot find the
original message, but here's the relevant bit:

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania
19073

--ELM1040320327-20624-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename="/bjm/diff"

Index: src/backend/utils/adt/numutils.c
===
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/numutils.c,v
retrieving revision 1.54
diff -c -c -r1.54 numutils.c
*** src/backend/utils/adt/numutils.c 4 Sep 2002 20:31:28 - 1.54
--- src/backend/utils/adt/numutils.c 19 Dec 2002 17:10:56 -
***
*** 70,76 
   if (s == (char *) NULL)
elog(ERROR, "pg_atoi: NULL pointer");
   else if (*s == 0)
!   elog(ERROR, "pg_atoi: zero-length string");
   else
l = strtol(s, &badp, 10);
  
--- 70,80 
   if (s == (char *) NULL)
elog(ERROR, "pg_atoi: NULL pointer");
   else if (*s == 0)
!  {
!   /* 7.3.X workaround for broken apps, bjm  2002-12-19 */
!   elog(WARNING, "pg_atoi: zero-length string");
!   l = (long) 0;
!  }
   else
l = strtol(s, &badp, 10);
  


--ELM1040320327-20624-0_
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0

Note however, that regression tests for 7.3.x will fail, as there are
explicit tests for zero-length strings to cause an error. That need not
worry you, though. We're currently running 7.3.4 with this patch and it
works like a charm.

Regards,

   Markus

> 

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] Obtain boolean value of expression in PLPGSQL

[Max Speransky]

Hello

My task is to validate expression and get value of it in boolean variable.
I try to do following:
CREATE OR REPLACE FUNCTION get_value(integer) RETURNS boolean AS'
DECLARE
Ret boolean;
Op  TEXT = ''='';
BEGIN

Ret := 3 || Op || $1;
RETURN Ret;
END;
' language 'plpgsql';

when I try to execute this function I get such result:

bill=# select get_value(3);
WARNING:  Error occurred while executing PL/pgSQL function get_value
WARNING:  line 6 at assignment
ERROR:  Bad boolean external representation '3=3'
bill=#

So, how can I explain that resulting type must be boolean ? Also, I try to
play with SELECT INTO with same result. 

-- 
... All opinions expressed are mine and not those of my employer.

Yours, Max   [Msg N 2419]
---
mailto: [EMAIL PROTECTED] phone: +380-44-2054455

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[GENERAL] pg7.3.4: pg_atoi: zero-length string

[Rob Fielding]

Hi,

We're currently experiencing a problem where SQL statements are failing 
when entring a '' for not not-null integer columns:

ERROR:  pg_atoi: zero-length string

This was discovered just after a database migration from 7.2 to 7.3.4.

Example:

insert into renewal_cache
(dom, expiry, issued, aid) values
('data','2004-03-05','19980305','')
The above example in just one case where 'aid' can accept a null value. 
The use of quotes around all values was established as IIRC pg7.2 
wouldn't accept statements without them. The use of this convention is 
extensive.

Cheers,

Rob Fielding
Development
Designer Servers Ltd


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string

[Rob Fielding]

Andrew Sullivan wrote:
On Tue, Nov 04, 2003 at 11:21:35AM +, Rob Fielding wrote:

Hi,

We're currently experiencing a problem where SQL statements are failing 
when entring a '' for not not-null integer columns:


Yes.  This behaviour was made more compliant in 7.3.  It's in the
release notes.

The above example in just one case where 'aid' can accept a null value. 
I've found this is a feature of 7.3 to not treat a empty string as a 
NULL integer type. Silly lazy me ;)

As it turned out it relatively trivial to fix the offending statements 
on the few occasions where it has been valid to do this.

Consider this a non-issue.

Cheers,

--

Rob Fielding
Development
Designer Servers Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] SELECT question

[Alex]

Hi,

I have a bit string , 7 bits, every bit representing a day of the week. 
e.g. 1110011.
Is there and easy way where I can translate/format that string in a query.
I want to give the string back with a '-' for every 0 and the first char 
of the Day for every '1'.
example 1100111 = SM--TFS.

thanks for any suggestions
Alex






---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string

[Bruno Wolff III]

On Tue, Nov 04, 2003 at 12:19:58 +,
  Rob Fielding <[EMAIL PROTECTED]> wrote:
> 
> I've found this is a feature of 7.3 to not treat a empty string as a 
> NULL integer type. Silly lazy me ;)

It didn't even then. It was treated as 0. Oracle is the DB that treats
empty strings as null values.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Very strange selectproblem

[Victor Spång Arthursson]

Can't select 3 columns from a table called varer.

The columns are the three first and are called vnummer, puNr and dNr.

The error message is:

indiadan=# select varer.vNummer from varer;
ERROR:  No such attribute varer.vnummer
indiadan=#
I'm probably doing something else wrong, but can't figure what…

Help needed,

regards,

Victor

---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] Databases with different encodings

[Jan Poslusny]

RedHat 9, PostgreSQL 7.3.4.
When I perform
initdb --locale=cs_CZ
createdb --encoding=latin2 lat
createdb --encoding=unicode uni
, the tables of lat are sorted fine, but the tables of uni not. When I 
initdb --locale=cs_CZ.UTF-8, sorts of uni are good, but sorts of lat 
not. Is some way to create two databases in the same database cluster 
with different encodings (specially utf-8 and iso 8859-2) and good 
sortings ?

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] Databases with different encodings

[Peter Eisentraut]

Jan Poslusny writes:

> Is some way to create two databases in the same database cluster
> with different encodings (specially utf-8 and iso 8859-2) and good
> sortings ?

No.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Very strange selectproblem

[Csaba Nagy]

See my comments below.

On Tue, 2003-11-04 at 13:10, Victor SpÃng Arthursson wrote:
> Can't select 3 columns from a table called varer.
> 
> The columns are the three first and are called vnummer, puNr and dNr.
> 
> The error message is:
> 
> indiadan=# select varer.vNummer from varer;
^^
this is upper case N
> ERROR:  No such attribute varer.vnummer
  ^^^
this is lower case n in vnummer

The problem could be that you created the table with the name of the vNummer column 
enclosed in double quotes, like this: "vNummer".
In this case postgres remembers the case of the field name. Names are case sensitive 
in postgres.
In your query, you don't enclose the field names in double quotes, like: 
varer.vNummer, which is folded to all lower case by postgres, and won't match the 
mixed case field you have created.
Try to quote the field names in the query, it will likely work.
A good practice to avoid such errors is to always use lower case names with postgres.

HTH,
Csaba.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Problem with Pg_dumpall

[Edwin Quijada]

Hi!
I am trying to do a dump all and when I do this I get this error
pg_dumpall: query failed: ERROR:  Unable to convert abstime 'invalid' to 
timestamp
pg_dumpall: query was: SELECT usename, usesysid, passwd, usecreatedb, 
usesuper, CAST(valuntil AS timestamp) FROM pg_shadow WHERE usesysid <> 
(SELECT datdba FROM pg_database WHERE datname = 'template0');

What is my problem???

_
Charla con tus amigos en línea mediante MSN Messenger: 
http://messenger.yupimsn.com/

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] Obtain boolean value of expression in PLPGSQL

[Pavel Stehule]

Hello

You are on wrong way.

Try like

DECLARE 
  b RECORD;
  s TEXT; 
  op TEXT;
BEGIN op := ''='';
  s := ''SELECT 3 ''|| op || ''|| $1 || ''AS b'';
  FOR b IN EXECUTE s LOOP
RETURN b.b;
  END LOOP;
END;

Regards
Pavel  


On Tue, 4 Nov 2003, Max Speransky wrote:

> Hello
> 
> My task is to validate expression and get value of it in boolean variable.
> I try to do following:
> CREATE OR REPLACE FUNCTION get_value(integer) RETURNS boolean AS'
> DECLARE
> Ret boolean;
> Op  TEXT = ''='';
> BEGIN
> 
> Ret := 3 || Op || $1;
> RETURN Ret;
> END;
> ' language 'plpgsql';
> 
> when I try to execute this function I get such result:
> 
> bill=# select get_value(3);
> WARNING:  Error occurred while executing PL/pgSQL function get_value
> WARNING:  line 6 at assignment
> ERROR:  Bad boolean external representation '3=3'
> bill=#
> 
> So, how can I explain that resulting type must be boolean ? Also, I try to
> play with SELECT INTO with same result. 
> 
> 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string

[Andrew Sullivan]

On Tue, Nov 04, 2003 at 12:19:58PM +, Rob Fielding wrote:
> I've found this is a feature of 7.3 to not treat a empty string as a 
> NULL integer type. Silly lazy me ;)

For the record, it _never_ treated it as NULL.  It treated it as
"empty string".  '' != NULL.  In fact, !(NULL=NULL) & !(NULL!=NULL). 
SQL uses three-valued logic.

A

-- 

Andrew Sullivan 204-4141 Yonge Street
Afilias CanadaToronto, Ontario Canada
<[EMAIL PROTECTED]>  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string

[Rob Fielding]

For the record, it _never_ treated it as NULL.  It treated it as
"empty string".  '' != NULL.  In fact, !(NULL=NULL) & !(NULL!=NULL). 
SQL uses three-valued logic.


You're absolutely right.  That explains why, when I quickly looked, some 
are zero's and some are NULLs - the NULLs where NULLs and the zeros 
where empty strings.

Two different bad-programming examples.  If I actually used these 
columns for anything whenever they didn't have non zero or null data in 
them then I'd have probably been alot more careful about what went in them.

I presume that an 32bit integer of zero and a NULL are represented 
differently in the database ?  I suppose internally you aren't 
representing a NULL within the context of a 32bit integer field and it 
would just probably be magic pointer to the next field - some sort of 
exercise in space squashing?  I don't know anything about the internal 
stucture of the tuples.

Dependant on the above, it would probably make sense to clean up the 
database, especially considering these columns are also indexed.

Cheers

--

Rob Fielding
Development
Designer Servers Ltd
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string

[Rob Fielding]

The above example in just one case where 'aid' can accept a null value. 
That's not a null.  It's a zero-length string.
I've found this is a feature of 7.3 to not treat a empty string as a 
NULL integer type. Silly lazy me.

As it turned out it relatively trivial to fix the offending statements 
on the few occasions where it has been valid to do this.

Consider this a non-issue.

Cheers,

--

Rob Fielding
Development
Designer Servers Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Problem with Pg_dumpall

[Tom Lane]

"Edwin Quijada" <[EMAIL PROTECTED]> writes:
> I am trying to do a dump all and when I do this I get this error

> pg_dumpall: query failed: ERROR:  Unable to convert abstime 'invalid' to 
> timestamp

IIRC it is possible to get this error in some upgrade scenarios if you
have pg_shadow rows whose "valuntil" field is "invalid".  Set those
entries to something else (NULL, perhaps) and you should be okay.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] PostgreSQL v7.4 Release Candidate 1

[Dennis Gearon]

Vivek Khera wrote:

"MGF" == Marc G Fournier <[EMAIL PROTECTED]> writes:
  

MGF> As we are in the home stretch of a full release, we encourage as 
many as
MGF> possible to test and report any bugs they can find, whether as 
part of the
MGF> build process, or running in "real life" scenarios.

MGF> If we've heard no reports back before midnight on Thursday, we 
are looking
MGF> at a full code freeze, with a Final Release to happen on the 
following
MGF> Monday.

Does this now guarentee no more initdb will be required?

 

do I have to be a major guru or coder to be on the dev list? I'd REALLY 
like to find out what the roadmap is for Postgres, especially in 
relationship to locale's, sorting, and encodings.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Constraint Problem

[Stephan Szabo]

On Tue, 4 Nov 2003, Ron St-Pierre wrote:

> Greg Stark wrote:
>
> >Ron St-Pierre <[EMAIL PROTECTED]> writes:
> >
> >
> >
> >>This is not quite what I need. I need to create a constraint to allow only
> >>-one- of
> >>company<->association<->default(=true) value
> >>but any number of
> >>company<->association<->default(=false) values
> >>
> >>
> >
> >So a unique index on "(company,association) where default" doesn't do what you
> >want?
> >
> No it doesn't. For example, after I create the unique index I can still
> input:
> company10 association7 true
> company10 association7 true
> company10 association7 true

You shouldn't be able to and I can't replicate similar behavior in a
simple test on 7.3.4.  I get "Cannot insert a duplicate key into unique
index" errors.

create table a(a int, b int, c boolean);
create unique index a_ind on a(a,b) where c;
insert into a values (1,1,true);
insert into a values (1,1,true);
insert into a values (1,1,false);
insert into a values (1,1,false);
insert into a values (1,2,true);

Where the second insert fails, but the others succeed.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] PostgreSQL v7.4 Release Candidate 1

[Marc G. Fournier]

On Tue, 4 Nov 2003, Dennis Gearon wrote:

> do I have to be a major guru or coder to be on the dev list?

Nope, anyone's welcome to join


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Constraint Problem

[Greg Stark]

Ron St-Pierre <[EMAIL PROTECTED]> writes:

> No it doesn't. For example, after I create the unique index I can still input:
> company10 association7 true
> company10 association7 true
> company10 association7 true
> I want to prevent this from happening, but still allow multiple
> company10 association7 false
> company10 association7 false
> entries for example.

For example:

test=# create table test (company integer, association integer, isdefault boolean);
CREATE TABLE
test=# create unique index testi on (company,association) where isdefault;
ERROR:  syntax error at or near "(" at character 30
test=# create unique index testi on test (company,association) where isdefault;
CREATE INDEX
test=# insert into test values (10,7,true);
INSERT 6888594 1
test=# insert into test values (10,7,true);
ERROR:  duplicate key violates unique constraint "testi"
test=# insert into test values (10,7,false);
INSERT 6888596 1
test=# insert into test values (10,7,false);
INSERT 6888597 1
test=# select * from test;
 company | association | isdefault 
-+-+---
  10 |   7 | t
  10 |   7 | f
  10 |   7 | f
(3 rows)

-- 
greg


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] PostgreSQL v7.4 Release Candidate 1

[Peter Eisentraut]

Marc G. Fournier writes:

> As we are in the home stretch of a full release, we encourage as many as
> possible to test and report any bugs they can find, whether as part of the
> build process, or running in "real life" scenarios.

Folks, we still need verification of PostgreSQL 7.4 on certain platforms.
See

for a list of what has already been tested.  In particular, if you have
any of the following platforms available,

IRIX
NetBSD (anything but ix86)
OpenBSD (anything but ix86 and sparc)
SCO OpenServer

please get the release candidate tarball and run ./configure; make all;
make check; make install on them to see whether everything works.  If you
have several compilers available (GCC and vendor compiler), try both.
Report the results together with `uname -a` to
[EMAIL PROTECTED]

-- 
Peter Eisentraut   [EMAIL PROTECTED]


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Constraint Problem

[Ron St-Pierre]

Greg Stark wrote:

Ron St-Pierre <[EMAIL PROTECTED]> writes:

 

This is not quite what I need. I need to create a constraint to allow only
-one- of
   company<->association<->default(=true) value
but any number of
   company<->association<->default(=false) values
   

So a unique index on "(company,association) where default" doesn't do what you
want?


 

No it doesn't. For example, after I create the unique index I can still 
input:
   company10 association7 true
   company10 association7 true
   company10 association7 true
I want to prevent this from happening, but still allow multiple
   company10 association7 false
   company10 association7 false
entries for example.

The idea of using NULLs is a good idea, but this is a production 
database and would require changes to the web-based front end (7 of 
them), not to mention each database. That's why I want to change the 
behaviour to only allow one unique company<-->association<-->TRUE 
combination. Right now there are a number of companies which have 
multiple default associations in the database, so I am going to have to 
back-trace and find out which association is actally the correct default.

BTW I am using postgresql 7.3.4

Ron

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] access linux command from SQL Query

[Mike Mascari]

Alam Surya wrote:

> hello all..
>  
> I have a litle question, do i have acces linux command from postgresql
> query like send message to others linux user in LAN ? any feature for that ?

You could use a system() call in a C function, or you could use Peter
Eisentraut's pl/sh language:

http://webmail.postgresql.org/~petere/plsh.html

Of course, if you use trigger functions to send email messages you
lose transactional integrity with respect to the underlying data vs.
the email message in the face of a transaction abort. Therefore some
people do this in middleware after the transaction associated with the
email message has been committed.

Hope that helps,

Mike Mascari
[EMAIL PROTECTED]



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster