Thread: Problem With A Rule

Hi!

When the following rule is created:

CREATE RULE rule1 AS ON UPDATE TO table1
DO UPDATE table2 SET column7=130 WHERE
column10=NEW.column10
AND column1=(SELECT column1 FROM table3  WHERE column10=NEW.column10 AND column4='N'AND column5=NEW.column1)
AND column6='2'
AND column8=OLD.column7 AND column9=OLD.column2;

then postgresql seems to be confused by the following
SQL such that psql keeps waiting for postgresql's
result:
update table1 set column6=130 where column2=1;

I have to ctrl-c psql to wake up psql.

However, the following rule works without problem:

CREATE RULE rule1 AS ON UPDATE TO table1
DO UPDATE table2 SET column7=130 WHERE
column10=NEW.column10
AND column1='OneConstant' AND column6='2'
AND column8=OLD.column7 AND column9=OLD.column2;

The following SQL also works fine:

update table2 set column7=130 where column10='1'
and column1=(SELECT column1 FROM table3 WHERE
column10='1' AND column4='N' AND column5='ID1') and column6='2' and column8='r'
and column9=1;

Does rule has problem with subselect or the above
problematic rule has bug?

Regards,

cn

--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com

<cnliou@eurosport.com> writes:
> When the following rule is created:
> ...
> then postgresql seems to be confused by the following
> SQL such that psql keeps waiting for postgresql's
> result:

Could we see a *complete* example?  It's no fun trying to
reverse-engineer your table declarations from just the rule;
let alone any related rules that might be involved, or the
data in the table if the behavior happens to be data-dependent.
        regards, tom lane

Greetings all,

I'm trying to insert values into a numeric field which may be optional. I'm
using perl's DBI and bind parameters to do this. However, psotgresql
complains that '' is not a numeric, since the DBI quotes everything before
inserting.. I've tried inserting NULL as well, but that gets quoted and
rejected as well.

Is there any way to do this without hard coding "NULL" into the prepared
statement handle?

Thanks!
Eric Carpenter

"Eric Carpenter" <postgresql@slacker.to> writes:

> Greetings all,
> 
> I'm trying to insert values into a numeric field which may be
> optional. I'm using perl's DBI and bind parameters to do
> this. However, psotgresql complains that '' is not a numeric, since
> the DBI quotes everything before inserting.. I've tried inserting
> NULL as well, but that gets quoted and rejected as well.
> 
> Is there any way to do this without hard coding "NULL" into the
> prepared statement handle?

It's been a long time since I used Perl with PostgreSQL but it seems
DBI will insert null values if you pass them variables that are
undefined.  Try using undef() and see what you get.

Good luck,
Jason