Re: [SQL] How to use BYTEA type?

[Radu-Adrian Popescu]

Doing

template1=> SELECT proname from pg_proc ;
proname

 boolin
 boolout
 byteain
 byteaout

you can see there is a byteaout function also. However, these are 
undocumented in the interactive docs, which is a shame indeed. I only 
learned of byteain/out after reading your email.

If some of the postgresql guys could point the location of the 
documentation of undocumented functions  :-)  that'd be great.


Radu-Adrian Popescu
DBA/programmer at www.aldratech.com


Christopher Sawtell wrote:

>Greetings folks,
>
>  Please could a kind soul tell be how to extract data from a BYTEA type of  
>field into a file, or better still tell me where I can find some doco?
>
>This is the table:-
>
>create table fax_info ( id serial, comment text, image bytea) ;
>
>This appears to work ( no erorr messages ):-
>
>chris=# insert into fax_info ( comment, image ) values
>( 'Faking it with a computer', byteain ('picture.pgm.gz'));
>INSERT 18772 1
>
>Is this correct?
>
>Now, how do I get my picture out again?
>
>TNX 10^6
>
>Sincerely etc.
>
>Christopher Sawtell.
>
>---(end of broadcast)---
>TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] PostgreSQL X Resin EE

[Radu-Adrian Popescu]

I have sucessfuly used resin (standard, not EE, 
with datasource, pooling) and postgresql.
=Radu-Adrian PopescuCSA, DBA, DeveloperAldratech 
Ltd.

  - Original Message - 
  From: 
  Pedro 
  Igor 
  To: [EMAIL PROTECTED] 
  Sent: Thursday, January 02, 2003 3:16 
  PM
  Subject: [SQL] PostgreSQL X Resin 
EE
  
  Have someone used Resin EE with PostgreSQL or 
  actually use both for building applications ? About PostgreSQL i know 
  that is one of the best options for back-end, but what about Resin 
  EE ?
   
  Thanks ...
   
  Pedro 
Igor 

[SQL] SQL function parse error ?

[Radu-Adrian Popescu]

Hello !
 
There is a strange behaviour in the SQL function 
parser (haven't tested this with the plpgsql lang) when using standard 
operators
and function parameters.
 
Take the following for example:
create or replace function 
testfunc1(int) returns setof test as'    select * from 
test where age>$1;'
language sql;
 
This yealds the 
following error, which frankly is kind of too 
much :
ERROR:  Unable to identify 
an operator '>$' for types 'integer' and 'integer'
 
I strongly belive the parser is a bit keen on 
interpreting the >$ sequence as an operator. Take for instance php, where all 
variables start with $; " 100>$foo "
is quite valid !
 
The error goes away of course when rewriting it as 
" age > $1".
 
I'm afraid that taking whitespace into 
consideration when parsing a language is not the way to do it, except when it's 
python, of course :)
 
Hope this, helps,

Regards, =Radu-Adrian PopescuCSA, 
DBA, DeveloperAldratech Ltd.

Re: [SQL] SQL function parse error ?

[Radu-Adrian Popescu]

I'm perfectly aware of the fact that a space solves the issue here.

What I'm saying is that it is not natural nor common to take some whitespace
into
account when parsing, since this is not bash language, nor python, as it
shouldn't be !
This is SQL, and people who are using PostgreSql write SQL, not
whitespace-sensitive SQL, bash or whatever.

And besides, like I have already pointed out, look at php's language parser
(behavior, not source) :
the statement if(100>$a) is perfectly legal, as it should be.
Is there any operator named >$ ?

Anyone who has used anything from Mysql to Oracle will get quite annoyed on
this one.

Hope the people in charge of the parser will get to the bottom of this...
... and please forgive my caustic tone.

=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

- Original Message -
From: "Tomasz Myrta" <[EMAIL PROTECTED]>
To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 1:56 PM
Subject: Re: [SQL] SQL function parse error ?


Radu-Adrian Popescu wrote:

>
> Take the following for example:
> create or replace function testfunc1(int) returns setof test as
> '
> select * from test where age>$1;
> '^^
> language sql;

I didn't find it in documentation, but sql functions are like bash (you
forgot about space character):

create or replace function testfunc1(int) returns setof test as
'
 select * from test where age > $1;
'
works fine.
Postgresql thinks that >$ is an operator instead of >

Tomasz Myrta


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]




---(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: [SQL] SQL function parse error ?

[Radu-Adrian Popescu]

Thomas, this line of conversation is not quite what one would expect...

I do read the postgresql docs when in run into trouble. And yes, i do know
there is such a thing as
operator overloading.

What i'm saying here, and i belive to be right, is that writing a piece of
code such as
table.columnName>$3
should not yeald an error.

Why is that ? Because the >$ does not exist, not in the default operator
list (also there is no operator defined
using $ anywhere within). And because whitespacing the code solves the
problem, which is rather thin, i must say.

Consider you would create operator >&. How should the parser interpret the
above piece of code ?
Operator or parameter ? If such a distinction is defined on strong grounds,
i have failed to see it stated anywhere
in the docs. Until then, based on experience with SQL code from alot of
dbms, c++ (operators, overloading, etc)
and php (where $name is a variable), i do belive the error reported is bogus
and unjustified.

As i was able to see from 7.3.1 docs, part of the operator documentation is
written by Tom Lane.
Perhaps some insight from the people in charge with SQL functions and/or
operators might help clear this issue.

Cheers,
=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

- Original Message -
From: "Tomasz Myrta" <[EMAIL PROTECTED]>
To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 3:22 PM
Subject: Re: [SQL] SQL function parse error ?


Radu-Adrian Popescu wrote:

> I'm perfectly aware of the fact that a space solves the issue here.
>
> What I'm saying is that it is not natural nor common to take some
> whitespace
> into
> account when parsing, since this is not bash language, nor python, as it
> shouldn't be !
> This is SQL, and people who are using PostgreSql write SQL, not
> whitespace-sensitive SQL, bash or whatever.
>
> And besides, like I have already pointed out, look at php's language
> parser
> (behavior, not source) :
> the statement if(100>$a) is perfectly legal, as it should be.
> Is there any operator named >$ ?
>
> Anyone who has used anything from Mysql to Oracle will get quite
> annoyed on
> this one.
>
> Hope the people in charge of the parser will get to the bottom of this...
> ... and please forgive my caustic tone.
>
> =
> Radu-Adrian Popescu
> CSA, DBA, Developer
> Aldratech Ltd.

I think you are absolutely wrong.
It is possible in Postgresql to overload operators and if you want, you
can create operator named ">$".

There is a lot of useful things in postgresql documentation. If you read
it, you could find this:

 >CREATE OPERATOR defines a new operator, name. The user who defines an
 >operator becomes its owner.
 >The operator name is a sequence of up to NAMEDATALEN-1 (31 by default)
 >characters from the following list:
 >+ - * / < > = ~ ! @ # % ^ & | ` ? $

Regards,
Tomasz Myrta


---(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




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

http://archives.postgresql.org

Re: [SQL] SQL function parse error ?

[Radu-Adrian Popescu]

Hello, nice to hear from you. I sent my message just before this one
arrived, sorry... :(
About the
>Nonsense.  SQL syntax is space-sensitive.  Or have you successfully
>written
>SELECTXFROMY;
>lately?
I do hope this is a joke. If not, it's an insult.
If it's not even that, then the joke's not on me, as it is quite obvious
that the "selectxfromy" counter-example
is bogus and childish.
Look at the following C code:
intmain(){return-10;}// no good
int main(){return-10;}// quite valid
Or the the following SQL code :
SELECT * from test where age>23;
SELECT * from test where age
>
23;

Now that will work on any given sql database, both queries. And the C
program will compile with most C compilers.
The fact that >$1 gets interpreted as an operator when there is no operator
>$ is nonsense.
I belive there is an issue here, and it's quite frustrating to see it
dismissed with such childish replies.
I have been working with PostgreSql for two years now, and i like it very
much. The one reason i sent out the first email
was to report something which striked me as odd in the first place (even if
it took under 10 seconds to get it fixed), something
that will make people just starting out with postgresql (like some of my
colleagues here) turn to me and laugh their heads off,
people having years of experience with, say, MSSql.

This is supposed to be constructive, not slaping eachother.
For that reason, i appologize for whatever harsh remarks i've made, and
simply hope to get a straight answer or even better a
conversation.

Regards,

=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 4:48 PM
Subject: Re: [SQL] SQL function parse error ?


"Radu-Adrian Popescu" <[EMAIL PROTECTED]> writes:
> This is SQL, and people who are using PostgreSql write SQL, not
> whitespace-sensitive SQL, bash or whatever.

Nonsense.  SQL syntax is space-sensitive.  Or have you successfully
written
SELECTXFROMY;
lately?

There has occasionally been talk of disallowing '$' as a valid character
in operator names, which would eliminate the syntactic ambiguity in this
example.  But undoubtedly it would also break a few applications that
use '$' in user-defined operator names, so the proposal hasn't passed
to date.

> Is there any operator named >$ ?

Whether there is one in the standard distribution is quite irrelevant.
It's a valid operator name according to the current rules, and so open
to definition by anyone who wants to.

The most recent discussion I can find about this is the pgsql-hackers
thread "Dollar in identifiers" from Aug 2001, eg
http://archives.postgresql.org/pgsql-hackers/2001-08/msg00629.php
There didn't seem to be a consensus to change things, so the old
behavior stands, for the moment.

regards, tom lane




---(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: [SQL] SQL function parse error ?

[Radu-Adrian Popescu]

Since you can overload and define new operators, the parser must - at some
point in time - lookup the operator definition.
It seems to me (but this is just an ideea), that the rules should go like
this :
...
check >$ is a defined operator
if true,
it is applied to left side and 1, in my example.
if not,
check that $1 is a valid expression (which it is)
...go on...
This should do the trick. And you would be able to write leftSide>$$1 and
would get the >$ operator applied to leftSide and $1.
And leftSide>&1 would apply the >& operator to 1, and my code would then
have a [very suble] bug.
Simply put, operator precedence over local identifiers/parameters, which i
belive is a de facto standard in most languages (no flames please !)
:)

Cheers,

=====
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
- Original Message -
From: "Achilleus Mantzios" <[EMAIL PROTECTED]>
To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 9:29 PM
Subject: Re: [SQL] SQL function parse error ?


On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:

>
>
> Why is that ? Because the >$ does not exist, not in the default operator
> list (also there is no operator defined
> using $ anywhere within). And because whitespacing the code solves the
> problem, which is rather thin, i must say.
>

Radu-Adrian,
i think the parser is built with yacc, (not "from scratch code") so
maybe finding if ">$" is in the specific DB's operators
would require code that whould slower the whole parsing
process (imagine what it means for performance).


==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-10-8981112
fax:+30-10-8981877
email:  [EMAIL PROTECTED]
[EMAIL PROTECTED]





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

http://archives.postgresql.org

Re: [SQL] SQL function parse error ?

[Radu-Adrian Popescu]

Please let me state again where i stand regarding this issue, apart from
tech stuff.
>From the viewpoint of someone who has worked with databases for quite some
time, including postgresql (2yrs),
and is making a living out of it, -- that would be me :) -- it is a very odd
and unpleasant behaviour. That's a simple fact.
However, the feeling slips away in about 5 minutes or so, even if i'm
writing all db scripts by hand and have to pay attention
to this quite often. That's because i like pgsql, i enjoy compiling,
testing, tweaking configuration, trying to push the load
thru the roof and stuff like that. But that's me. I am not at all bothered
by this issue anymore.
What i'm saying is that i know that some of my colleagues, nice guys for
that matter, and good programmers, will come screaming
to me "what's with the b.s. error ?!?", and when i'll tell them that the sql
parser belives that's an inexisting operator, they'll start
cursing at it, just like i did.
For what it's worth, some policy should be enforced, because it shouldn't
matter how many spaces you put between the operator
and the operand, as writing SELECT * is the same as SELECT
*.
I rest my case.

Cheers,
=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.


- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Achilleus Mantzios" <[EMAIL PROTECTED]>
Cc: "Radu-Adrian Popescu" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 5:57 PM
Subject: Re: [SQL] SQL function parse error ?


Achilleus Mantzios <[EMAIL PROTECTED]> writes:
> On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:
>> Why is that ? Because the >$ does not exist, not in the default operator
>> list

> i think the parser is built with yacc, (not "from scratch code") so
> maybe finding if ">$" is in the specific DB's operators
> would require code that whould slower the whole parsing
> process (imagine what it means for performance).

There are a couple of good reasons why parsing strings into tokens does
not depend on looking to see which operators actually exist (as opposed
to which ones *could* exist per the defined rules for operator names):

1. It'd be impractical to detect whether the effective parsing rules are
complete or consistent, if they depend on the contents of database
tables that will vary from one installation to another.

2. The lexer and grammar stages of parsing cannot look into the database
state, because they have to be executable outside a transaction.
Otherwise we'd have problems with detecting/processing BEGIN, COMMIT,
ROLLBACK statements.

(Speed would probably be a significant issue too, though I don't have
any hard facts to back up that feeling.  We'd definitely have to abandon
the use of lex/flex tools to generate the lexing code.)

Because of these issues, the question of whether ">$" actually is
defined as an operator in a particular installation is irrelevant to
how we split character strings into tokens.  The only way we have to
adjust this behavior is by changing the rules about what an operator
name could be, for everyone.

regards, tom lane

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




---(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: [SQL] SQL function parse error ?

[Radu-Adrian Popescu]

Nice to see that things are starting to move.
I was wandering however whether I've succeeded in making a point.

Regards, 
=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

- Original Message - 
From: "Tom Lane" <[EMAIL PROTECTED]>
Subject: Re: [SQL] SQL function parse error ? 


Stephan Szabo <[EMAIL PROTECTED]> writes:
> Although the rules could be similar to those for + and - at the end of
> operator strings (no $ at the end of an operator unless it contains
> characters not normally in SQL92 operators).  I'm not sure that
> behavior is sensible either, but if someone wanted to
> do it for their own installation it's about a 2 line patch.

It could be done that way.  But given that "$" already has one weird
special case in the operator name rules (ie, it can't be the only
character of an operator name), I feel that we'd be making things overly
complicated.

The proposal back in Aug 2001 was to remove "$" from the set of operator
name characters altogether (which would allow us to use it in
identifiers instead, improving Oracle compatibility).  I originally
objected to that idea on backwards-compatibility grounds, but I'm
leaning more and more to the view that it's the right thing to do.

I've re-opened the thread on pgsql-hackers about this, and we'll see
whether any consensus emerges this time.

regards, tom lane




---(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: [SQL] SQL function parse error ?

[Radu-Adrian Popescu]

Robert, my dear fellow...

How about checking your facts before contradicting anyone ? Shame on you !
Have you actually tried to do a SELECT* from foo ? Pathetic !
Try it out, if that won't work on Oracle, MSSQL, PostgreSql I'll buy
everyone on this list a chase of Crystal.
Robert, even
select*from errors;
works on all three of them ! (Sorry about the colors, pasted from TOAD).
Here's psql for instance:

db=# SELECT * from prod ;
 id | name | price  | group_id
+--++--
  4 | some product |  55.00 |3
  6 | MyPC | 300.00 |3
(2 rows)
db=# SELECT*from prod ;
 id | name | price  | group_id
+--++--
  4 | some product |  55.00 |3
  6 | MyPC | 300.00 |3
(2 rows)

There you go. Now go sit in the corner ! :)
Also, as I tried to make it quite clear, the point is not whether Oracle or
mysql allow the
use of $ in operators, but whether you have to write cumbersome syntax to
get things working. And the
answer is definitely _no_.
My point here is that common sense (and the use of $1, $2,... with operator
> is going
come up a lot, opposed to the user-defined operator >$, which takes
precedence when parsing a special
case of the SQL command) should prevail over backwards compat. Loot at C++
for instance, the standard
broke a lot of C++ apps that were written poorly.

It seems that - strangely - instead of trying to acknowledge not necessarily
incorrect but awkward behavior,
some people on this list have tried to put me down.
What's even more scary is receiving answers like "SQL queries are like bash
commands",
or "SELECTXFROMY is not valid - whitespace matters" (when in fact I was
simply pointing out that
i==3 and i == 3 should be parsed alike), or Robert's claim that SELECT* from
... is invalid SQL.

The good news is some people seem to have gotten the point and are doing
something about it - and this
makes me feel like maybe, maybe I've helped the community just a little bit.
After all, we all want to see
postgresql up there where it belongs.

That being said, I do hope that superficial replies trying to prove me wrong
will stop, as they actually don't help
anyone.

Regards,
=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

- Original Message -----
From: "Robert Treat" <[EMAIL PROTECTED]>
To: "Radu-Adrian Popescu" <[EMAIL PROTECTED]>
Cc: "Achilleus Mantzios" <[EMAIL PROTECTED]>; "Tom Lane"
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 10:44 PM
Subject: Re: [SQL] SQL function parse error ?


On Thu, 2003-01-09 at 11:29, Radu-Adrian Popescu wrote:
> What i'm saying is that i know that some of my colleagues, nice guys for
> that matter, and good programmers, will come screaming
> to me "what's with the b.s. error ?!?", and when i'll tell them that the
sql
> parser belives that's an inexisting operator, they'll start
> cursing at it, just like i did.
>

Does oracle or mysql or whichever db you like allow the use of $ in user
defined operators?  If so, how do they know the difference?

 For what it's worth, some policy should be enforced, because it shouldn't
> matter how many spaces you put between the operator
> and the operand, as writing SELECT * is the same as SELECT
> *.
> I rest my case.
>

Thats an invalid comparison.  The problem is not that foo > $1
doesn't work, as your example put forth. The problem is that foo>$1
doesn't work, which by comparison would be SELECT* which would also not
work.

Robert Treat






---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] insert rule doesn't see id field

[Radu-Adrian Popescu]

To everyone interested, check out Tom Lane's and Bruce's comments on
pgsql-hackers:
http://archives.postgresql.org/pgsql-hackers/2003-01/msg00446.php
There seems to be some consensus towards removing $ from the list of allowed
operator characters.

Regards,
=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

- Original Message -
From: "Ron Peterson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 9:12 PM
Subject: Re: [SQL] insert rule doesn't see id field


On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote:
> On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote:
>
> > > I thought that the idea behind noup was to protect single columns from
> > > update.  However, when I apply the noup trigger as above, I can't
> > > update /any/ column.  Is this the intended behaviour?
> >
> > Idly looking at the source code for contrib/noupdate/noup.c, I don't
> > believe that it has ever worked as advertised: it seems to reject any
> > non-null value for the target column, independently of whether the
> > value is the same as before (which is what I'd have thought it should
> > do).
> >
> > Is anyone interested in fixing it?  Or should we just remove it?
> > If it's been there since 6.4 and you're the first person to try to use
> > it, as seems to be the case, then I'd have to say that it's a waste of
> > space in the distribution.
>
> I'm going to see if I can create this function.

Well, I think I've thunk something up.  Of course I'm happy to submit
my modification for distribution or ridicule, as the case may be.
Where should I submit this?

I made a function noupcols() which takes one or more column names as
arguments.  The function then creates a new tuple by getting the old
values for those columns, and then doing an SPI_modifytuple on the new
tuple using the old values for those columns.

I'm kind of flying by the seat of my pants here, so if anyone would
care to critically review my code, by all means...

--
Ron Peterson  -o)
Network & Systems Manager /\\
Mount Holyoke College_\_v
http://www.mtholyoke.edu/~rpeterso   

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

http://www.postgresql.org/users-lounge/docs/faq.html




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

Re: [SQL] insert rule doesn't see id field

[Radu-Adrian Popescu]

I'm extremely sorry about the post in this thread ! Had a brain cramp, my
appologies. Should have been Re: [SQL] SQL function parse error.
Terribly sorry again !

=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
- Original Message -
From: "Radu-Adrian Popescu" <[EMAIL PROTECTED]>
To: "Ron Peterson" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, January 10, 2003 11:31 AM
Subject: Re: [SQL] insert rule doesn't see id field


To everyone interested, check out Tom Lane's and Bruce's comments on
pgsql-hackers:
http://archives.postgresql.org/pgsql-hackers/2003-01/msg00446.php
There seems to be some consensus towards removing $ from the list of allowed
operator characters.

Regards,
=
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

- Original Message -
From: "Ron Peterson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, January 09, 2003 9:12 PM
Subject: Re: [SQL] insert rule doesn't see id field


On Wed, Jan 08, 2003 at 01:13:03PM -0500, Ron Peterson wrote:
> On Tue, Jan 07, 2003 at 11:01:08AM -0500, Tom Lane wrote:
>
> > > I thought that the idea behind noup was to protect single columns from
> > > update.  However, when I apply the noup trigger as above, I can't
> > > update /any/ column.  Is this the intended behaviour?
> >
> > Idly looking at the source code for contrib/noupdate/noup.c, I don't
> > believe that it has ever worked as advertised: it seems to reject any
> > non-null value for the target column, independently of whether the
> > value is the same as before (which is what I'd have thought it should
> > do).
> >
> > Is anyone interested in fixing it?  Or should we just remove it?
> > If it's been there since 6.4 and you're the first person to try to use
> > it, as seems to be the case, then I'd have to say that it's a waste of
> > space in the distribution.
>
> I'm going to see if I can create this function.

Well, I think I've thunk something up.  Of course I'm happy to submit
my modification for distribution or ridicule, as the case may be.
Where should I submit this?

I made a function noupcols() which takes one or more column names as
arguments.  The function then creates a new tuple by getting the old
values for those columns, and then doing an SPI_modifytuple on the new
tuple using the old values for those columns.

I'm kind of flying by the seat of my pants here, so if anyone would
care to critically review my code, by all means...

--
Ron Peterson  -o)
Network & Systems Manager /\\
Mount Holyoke College_\_v
http://www.mtholyoke.edu/~rpeterso   

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

http://www.postgresql.org/users-lounge/docs/faq.html




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




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

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Catching DML exceptions in PL/pgSQL

[Radu-Adrian Popescu]

Hello all,
(and sorry if this has been aswered before)
Take this piece of code for example:
.
begin
    _res.code:=1;
    select id into iid from log where id=_id;
    if not found then begin
    _res.msg:=''insert'';
    insert into log (log, data)
values (_log, _data);
    if not found then
begin
   
_res.msg:=_res.msg || '' error'';
   
_res.code:=-1;
    end;
    end if;
    end;
    else begin
.
The thing is if _data (parameter) is null and table has a (data <>
null) check, the insert would fail and abort the function before my
"if not found" test.
I'm porting a java app. from mssql to postgresql, and the java code
relies on the stored procedure to always return it's status (in _res.code
in this case).
Is there anything I can do to make sure the function always returns _res
?
Something along the lines of Oracle's exception handling, or the @@error
trick in mssql ?


Regards,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.

Re: [SQL] Catching DML exceptions in PL/pgSQL

[Radu-Adrian Popescu]

At  6/17/2003 11:44, Tomasz Myrta wrote:

Dnia 2003-06-17 11:25, U¿ytkownik Radu-Adrian Popescu napisa³:

Hello all,
(and sorry if this has been aswered before)
Take this piece of code for example:
.
begin
_res.code:=1;
select id into iid from log where id=_id;
if not found then begin
_res.msg:=''insert'';
*insert into log (log, data) values (_log, _data);
if not found* then begin
_res.msg:=_res.msg || '' error'';
_res.code:=-1;
end;
end if;
end;
else begin
.
The thing is if _data (parameter) is null and table has a (data <> null) 
check, the insert would fail and abort the function before my "if not 
found" test.
I'm porting a java app. from mssql to postgresql, and the java code 
relies on the stored procedure to always return it's status (in _res.code 
in this case).
Is there anything I can do to make sure the function always returns _res ?
Something along the lines of Oracle's exception handling, or the @@error 
trick in mssql ?
You can't do it this way. Your insert is violation of some constraint 
(problably "not null" or "primary key" constraint). This kind of violation 
raises exception and whole transaction is aborted. I don't use java, but C 
librares raises also ordinary C exception which can be easily caught. If 
you want to avoid such cases - check your data before inserting them.
Thanks, unfortunatelly I knew that...
My java/db code currently decides whether the call was successful based on
1) return from stored procedure (currently, to be replaced by select * from 
function_name(...))
2) SQLException
So basically it would work no probs: i.e. instead of reading a -1 when an 
error occured, there would be an SQLException caught and the
final outcome, either way, would be that the java method would still 
function properly and let the calling code know whether things went okay
or not.
However, the point was to be able to deal with these exceptions inside 
pl/plgsql, as it would bring imho a load more power and fexibility to the 
db code.
For instance, it could choose to log failures to a database table, or 
choose another execution path and so on.

Anyway, thanks for the reply :-)

--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd. 



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Catching DML exceptions in PL/pgSQL

[Radu-Adrian Popescu]

Joe, Tomasz,

Thanks for the reply.
In any event, like I said, my java code should work with the functions as 
they are now, whether db throws an exception or returns some result.
Also, the oracle-like exception handling would be really great and i know 
i'm looking forward to it !
Maybe in 7.5 ?
:-)

Cheers,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd. 



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

Re: [SQL] Catching DML exceptions in PL/pgSQL

[Radu-Adrian Popescu]

At  6/17/2003 08:33, Josh Berkus wrote:

Radu,

> In any event, like I said, my java code should work with the functions as
> they are now, whether db throws an exception or returns some result.
> Also, the oracle-like exception handling would be really great and i know
> i'm looking forward to it !
> Maybe in 7.5 ?
Unfortunately, we seem to still lack a dedicated PL/pgSQL source developer on
the project.   Know anybody?
Unfortunatelly yacc and myself are not good friends, at least not yet :-)
Aside from that, this is pretty bad news for me :-(
PEOPLE HELP OUT PL/pgSQL !!! :-O
Cheers,

--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd. 



---(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: [SQL] returning a recordset from PLpg/SQL

[Radu-Adrian Popescu]

Terence Kearns wrote:

Tom Lane wrote:

> Stephan Szabo <[EMAIL PROTECTED]> writes:
>
>> On Tue, 2 Mar 2004, Terence Kearns wrote:
>>
>>> Well I haven't yet done anything because I couldn't get anything to
>>> compile which returned SETOF RECORD..
>
>
>
>> As a starting point, SETOF "RECORD" is different from SETOF RECORD 
given
>> PostgreSQL's fold case to lower case for unquoted names.
>
>
>
> Also, you can hardly expect a function to return a rowtype that doesn't
> even exist until the function executes --- how the heck is the parser
> supposed to make sense of the calling query?  So the "execute create
> type" part of this is nonsense, I'm afraid.

Right you are. I did mention that I didn't expect that code to work at 
all, I just used it as an indicator or a desired outcome. I'm sorry if 
I didn't make that clear enough. I just hoped that it would illustrate 
what I'm trying to achieve. And that is:
"return a set of rows where the columns in that row are not yet 
determined."

> The SETOF RECORD mechanism

> will let you return a rowtype that is not known fully at the time the
> function is written, but the rowtype does have to be known when the
> calling query is parsed.
Interesting.

>
> You might be able to replace the CREATE TYPE with an anonymous record
> type in the calling query:
>
> select ...
> from details_for_profile(...) as x(doc_id int4,
>doc_title varchar(256),
>...);
>
> regards, tom lane
>
yeah but then you're back to square one with dynamically building the 
columns to match the anonymous type you declared in the query.

I've got some ideas about creating persistent types using RULEs on the 
attribute_profiles table. So when someone INSERTs or UPDATEs an 
attribute profile, a datatype going by the name 'profile_type_' || 
att_profile_id::text is created. That way the types are already known 
and maybe can somehow be passed to the details_for_profile() prcedure.

I'll have to experiment and get back.

I'm sorry if this is just plain stupid - as I've not bothered to read 
the full schema in your message - but wouldn't a cursor do ?
Since I'm mostly coding in Java, I find that this approach, while common 
to both PGSQL and Oracle, works quite good. I've not
been able to use the cursor in psql although... By the way can someone 
tell me how the heck am I supposed to get the results
from a cursor return from a pl/pgsql function ?

Example:
create or replace function SomeObject_read(int) returns refcursor as
   '
   declare
   the_row refcursor;
   v_id alias for $1;
   begin
   open the_row for
   select *
   from SomeObject
   where id = v_id;
   return the_row;
   end;
   ' language plpgsql;
This works great from Java where I use a callable statement, do a "{ ? = 
call SomeObject_read(?) }" query,
register the out parameter as OTHER, bind the parameter and get back a 
ResultSet.
All fine and marry, but I can't figure out how to use _this_ form of the 
function from psql. I've read and tried
the manual examples and they do work. However, I can't make this one work.

Thanks in advance,

--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
+40213212243


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

Re: [SQL] Login information in system tables

[Radu-Adrian Popescu]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Daniel Doorduin wrote:

| Hi,
|
| I was wondering if it is possible to query the sytem tables to get
| an overview of user logins in psql.
Check out the pg_user table.

Regards,
- --
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
+40213212243
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFATGvDVZmwYru5w6ERAlJ7AJ9RLaEkboJ+YNuwEcmur/OXnhlB2ACgoXum
GEy4Lun4Gtmi3FJkWFby5T8=
=4JRJ
-END PGP SIGNATURE-


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

Re: [SQL] feature request ?

[Radu-Adrian Popescu]

Bruno Wolff III wrote:
There already is a syntax like this. You can do:
CASE boolean_expression
  WHEN TRUE THEN whatever
  WHEN FALSE THEN whatever
  ELSE whatever
END
Besides, sad, there's no such thing as a tri-valued boolean.
You either have a boolean(with a true/false value) or a NULL, which is something 
completely different and it's _not_ one of the two _values_ that a boolean 
object takes. I think you're looking at this completely wrong. If in Java you 
receive a Boolean object instance, will you start claiming that that Java has 
tri-valued booleans, and the "if" should account for that ?!

You can think of values in plpgsql as wrapper objects that carry a value and 
have a "is_null" flag; I have no idea how they're implemented in PostgreSQL or 
in any RDMBS in general but this should do it, at least for a naive implementation.

Peace,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] feature request ?

[Radu-Adrian Popescu]

sad wrote:
You can think of values in plpgsql as wrapper objects that carry a value
and have a "is_null" flag; I have no idea how they're implemented in
PostgreSQL or in any RDMBS in general but this should do it, at least for a
naive implementation.

Why should i think on simple object MUCH more complicated than it in nature is 
?
Let we discuss plpgsql here. Leave the Java to Javers.


I _am_ discussing plpgsql here. And I'm also trying to shed some light on this one.
Now, you think of these things as "MUCH more complicated" because they are NOT 
simple objects, and if you think that having an integer value that can be NULL 
is complicated, perhaps you should stick to assembly.
Now, I'm no specialist on this, but it is quite obvious that any data type 
instance in SQL can have values from:
- its natural domain (arrays of characters, numbers, ip addresses, dates, binary 
data and so on)
- NULL

Now think of where else you've seen this. It's quite obvious that
"function foo(int)" in plpgsql is like "void foo(Integer i)" or like "void 
foo(int* i)" and NOT like "void foo(int i)". Now the latter does not put any 
problems to anyone, does it ? You check that the reference is set and then 
proceed to use it's value.

Also you might consider giving it a rest and stop shouting in the forum - it's 
not your back yard you know.

Peace,
--
Radu-Adrian Popescu
CSA, DBA, Developer
Aldrapay MD
Aldratech Ltd.
+40213212243
---(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