error in SELECT

"P.M" <pmdanger@yahoo.com> writes:

here is my function in PostgreSQL :
CREATE OR REPLACE FUNCTION immense_sp001(VARCHAR,VARCHAR, service_nom VARCHAR, OUT result BOOLEAN, OUT error_message VARCHAR)
RETURNS record LANGUAGE plpgsql
AS '
BEGIN
DECLARE
username varchar :=$1;
strhash varchar :=$2;
Profile_Detected INTEGER;
Service_Already_Exist INTEGER;

/* detect if the user logged in exists in database */
SELECT count(*) INTO Profile_Detected FROM profiles WHERE login=username AND pwd=strhash;

The BEGIN goes after the variable declarations, not before them.

You're going to have some problems with those double-quoted string
literals too; that's not the correct syntax for string literals.
And you don't use "set" when assigning to a plpgsql variable.

Might be a good idea to practice on some toy functions until you've got
some familiarity with plpgsql syntax, rather than diving in with porting
many-line functions. A big function is too hard when you're trying to
fix many misconceptions at once.

regards, tom lane

On Sat, May 27, 2006 at 08:20:47AM -0700, P.M wrote:

i've just migrated a stored procedure from MySQl to PostgreSQL and i have the following error :
ERROR: syntax error at or near "SELECT" at character 371

here is my function in PostgreSQL :
CREATE OR REPLACE FUNCTION immense_sp001(VARCHAR,VARCHAR, service_nom VARCHAR, OUT result BOOLEAN, OUT error_message VARCHAR)
RETURNS record LANGUAGE plpgsql
AS '
BEGIN
DECLARE

BEGIN should follow the variable declarations. See "Structure of
PL/pgSQL" in the documentation:

http://www.postgresql.org/docs/8.1/interactive/plpgsql-structure.html

The function also has other MySQLisms such as auto_increment and the
use of "set". See the PL/pgSQL and other PostgreSQL documentation
for the appropriate syntax.

--
Michael Fuhr

Hi Tom,

in fact,i needed to move the DECLARE before begin and to adjust some autoincrement...and the problem is solved ;-)

thx,

Maileen

Tom Lane <tgl@sss.pgh.pa.us> wrote: "P.M"
writes:

here is my function in PostgreSQL :
CREATE OR REPLACE FUNCTION immense_sp001(VARCHAR,VARCHAR, service_nom VARCHAR, OUT result BOOLEAN, OUT error_message VARCHAR)
RETURNS record LANGUAGE plpgsql
AS '
BEGIN
DECLARE
username varchar :=$1;
strhash varchar :=$2;
Profile_Detected INTEGER;
Service_Already_Exist INTEGER;

/* detect if the user logged in exists in database */
SELECT count(*) INTO Profile_Detected FROM profiles WHERE login=username AND pwd=strhash;

The BEGIN goes after the variable declarations, not before them.

You're going to have some problems with those double-quoted string
literals too; that's not the correct syntax for string literals.
And you don't use "set" when assigning to a plpgsql variable.

Might be a good idea to practice on some toy functions until you've got
some familiarity with plpgsql syntax, rather than diving in with porting
many-line functions. A big function is too hard when you're trying to
fix many misconceptions at once.

regards, tom lane

---------------------------------
Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2�/min or less.

Hi All,

This is ambarasing, but I've just noticed the following (which looks
inconsistant to inexperienced eye). Having a table:

test=> CREATE TABLE xxx (id int, info text);

With some rows in it, I try:

test=> SELECT count(1) from xxx where id=1;
count
-------
0
(1 row)

This is correct (meaning, I expected that). But when I try to fetch the
actual selector used in the query as well, I get no rows instead.

test=> SELECT count(1),id from xxx where id=1 group by id;
count | id
-------+----
(0 rows)

Is this a feature, or a bug? And in fact, is there a construct to get
both the count() and its selectors *in*case*, when the count is ZERO?

All the above in postgres 8.1.

Thenx.

--
-R

Rafal Pietrak wrote:

Hi All,

This is ambarasing, but I've just noticed the following (which looks
inconsistant to inexperienced eye). Having a table:

test=> CREATE TABLE xxx (id int, info text);

With some rows in it, I try:

test=> SELECT count(1) from xxx where id=1;
count
-------
0
(1 row)

This is correct (meaning, I expected that). But when I try to fetch the
actual selector used in the query as well, I get no rows instead.

test=> SELECT count(1),id from xxx where id=1 group by id;
count | id
-------+----
(0 rows)

Is this a feature, or a bug? And in fact, is there a construct to get
both the count() and its selectors *in*case*, when the count is ZERO?

All the above in postgres 8.1.

It is supposed to work that way. In the first query, we have to return
a row to show you the count, while in the second query, there is no 'id'
value to show you, so we return nothing (nothing to GROUP BY).

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Sat, 2006-05-27 at 14:06 -0400, Bruce Momjian wrote:

Is this a feature, or a bug? And in fact, is there a construct to get
both the count() and its selectors *in*case*, when the count is ZERO?

All the above in postgres 8.1.

It is supposed to work that way. In the first query, we have to return
a row to show you the count, while in the second query, there is no 'id'
value to show you, so we return nothing (nothing to GROUP BY).

But is there a way to achieve one row output with both the count() and
its selector, when the ocunt is ZERO?

I'm digging this, because it looke like I need a VIEW, that returns such
count() no matter what. And in fact the selector (which is coming from
subquery) is more important for me in that case, than the count() itself
(well, I need to distinquish zero from something, but nothing more).

Is there a way to see it?

--
-R

Maybe this my work for you?

CREATE TABLE xxx (id int, info text);
create table xxx_grp ( id int, grp text);

insert INTO xxx_grp values ( 0, 'group0');
insert INTO xxx_grp values ( 1, 'group1');
insert INTO xxx_grp values ( 2, 'group2');

insert into xxx values ( 1, 'test1');
insert into xxx valves ( 2, 'test2a');
insert into xxx values ( 2, 'test2b');

select count(xxx.id) as cnt,
xxx.id,
xxx_grp.id as grpid,
xxx_grp.grp
from xxx
right join xxx_grp
on (xxx.id = xxx_grp.id)
group by xxx.id, grpid, xxx_grp.grp
order by xxx_grp.id;

cnt | id | grpid | grp
-----+----+-------+--------
0 | | 0 | group0
1 | 1 | 1 | group1
2 | 2 | 2 | group2

regards,

Richard Broersma Jr.

--- Rafal Pietrak <rafal@zorro.isa-geek.com> wrote:

On Sat, 2006-05-27 at 11:51 -0700, Richard Broersma Jr wrote:

select count(xxx.id) as cnt,
xxx.id,
xxx_grp.id as grpid,
xxx_grp.grp
from xxx
right join xxx_grp
on (xxx.id = xxx_grp.id)
group by xxx.id, grpid, xxx_grp.grp
order by xxx_grp.id;

cnt | id | grpid | grp
-----+----+-------+--------
0 | | 0 | group0
1 | 1 | 1 | group1
2 | 2 | 2 | group2

The count() in my case may easyly reach 10k (so the group table wont end
up too light, and it have to have a margin), but that's something
concrete to start with.

Thenx.

--
-R

Rafal Pietrak wrote:

On Sat, 2006-05-27 at 14:06 -0400, Bruce Momjian wrote:

Is this a feature, or a bug? And in fact, is there a construct to get
both the count() and its selectors *in*case*, when the count is ZERO?

All the above in postgres 8.1.

It is supposed to work that way. In the first query, we have to return
a row to show you the count, while in the second query, there is no 'id'
value to show you, so we return nothing (nothing to GROUP BY).

But is there a way to achieve one row output with both the count() and
its selector, when the ocunt is ZERO?

I'm digging this, because it looke like I need a VIEW, that returns such
count() no matter what. And in fact the selector (which is coming from
subquery) is more important for me in that case, than the count() itself
(well, I need to distinquish zero from something, but nothing more).

Is there a way to see it?

SELECT dummy.id, count(xxx.id) FROM (SELECT :id as id FROM dual) as
dummy LEFT JOIN xxx using (id) GROUP BY id;

You owe the Oracle a natural left join replacement.

/Nis

On Mon, 2006-05-29 at 12:32 +0200, Nis Jorgensen wrote:

Rafal Pietrak wrote:

But is there a way to achieve one row output with both the count() and
its selector, when the ocunt is ZERO?

SELECT dummy.id, count(xxx.id) FROM (SELECT :id as id FROM dual) as
dummy LEFT JOIN xxx using (id) GROUP BY id;

You owe the Oracle a natural left join replacement.

Luckily I've already figured that out, after Richard hinted me on using
a JOIN.

Thenx!

--
-R

Hi!

Within a UTF-8 encoded database, I have a table:
CREATE TABLE pics (id serial not null unique, img bytea);

The table is originally initialized with a set of IDs. Then I'm using
perl-script to insert apropriate images by means of UPDATEing rows:
--------------within my script called 'job'-------------------
my $db = DBI->connect('DBI:Pg:dbname=mydb') or die "DBI";
my $z = $db->prepare("UPDATE pics set img=? where id=?") or die
"PREPARE";
my $rc = $z->execute($content, $FILEID) or die "EXEC";
---------------------

But the result is somewhat unexpected:
---------------console output----------------------
DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding
"UTF8": 0x89
EXEC at ./job line 22, <> chunk 1.
---------------------------------

How come the bytearea is *interpreted* as having encoding?

Or to put it the other way around: What column datatype should I use for
an opoque binary value?

(my postgres is 8.1.4)

--
-R

Am Montag, 29. Mai 2006 13:35 schrieb Rafal Pietrak:

How come the bytearea is *interpreted* as having encoding?

If you pass data in text mode, all data is subject to encoding handling. If
you don't want that, you need to use the binary mode.

Or to put it the other way around: What column datatype should I use for
an opoque binary value?

bytea is the one.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Mon, May 29, 2006 at 01:35:58PM +0200, Rafal Pietrak wrote:

The table is originally initialized with a set of IDs. Then I'm using
perl-script to insert apropriate images by means of UPDATEing rows:
--------------within my script called 'job'-------------------
my $db = DBI->connect('DBI:Pg:dbname=mydb') or die "DBI";
my $z = $db->prepare("UPDATE pics set img=? where id=?") or die
"PREPARE";
my $rc = $z->execute($content, $FILEID) or die "EXEC";
---------------------

But the result is somewhat unexpected:
---------------console output----------------------
DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding
"UTF8": 0x89
EXEC at ./job line 22, <> chunk 1.
---------------------------------

How come the bytearea is *interpreted* as having encoding?

Actually, it's not the bytea type that is being interpreted, it's the
string you're sending to the server that is. Before you send bytea data
in a query string, you have to bytea encode it first. The DBD::Pg
manpage seems to suggest something like:

$rv = $sth->bind_param($param_num, $bind_value,
{ pg_type => DBD::Pg::PG_BYTEA });

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Mon, 2006-05-29 at 14:01 +0200, Martijn van Oosterhout wrote:

How come the bytearea is *interpreted* as having encoding?

Actually, it's not the bytea type that is being interpreted, it's the
string you're sending to the server that is. Before you send bytea data
in a query string, you have to bytea encode it first. The DBD::Pg
manpage seems to suggest something like:

$rv = $sth->bind_param($param_num, $bind_value,
{ pg_type => DBD::Pg::PG_BYTEA });

Hmmm, despite initial euphoria, this doesn't actually work.

Subsequently I've also tried putting SQL_BINARY in place of that
hash-ref, and plain DBD::Pg::PG_BYTEA, and also I tried to use 'TYPE =>'
instead of pg_type. (All those hints in man DBI). None of that worked
either.

But I also did:
$db->do('SET client_encoding = LATIN1') or die "SET";
just after connect and before prepare, and this produced a slightly
different result.... no ERROR, but the image was cut short to 9-bytes
inside the database data-row.

Would perl have interpreted this command according to it's semantics?
And change it's own default string handling accordingly!?

Not knowing the internals, I wouldn't bet on whichever, but I have my
doughts - my quess is thet DBI driver doesn't go that far. So if it
hasn't interpretted the 'SET client_encodding' internally, but just
passed that to database, the only thing that changed is the database
frontend context.

So may be the original error came from the database itself anyway?

Any ideas? (still hopping I wont have to write a C-level interface
function just to test what's really happening.... :)

--
-R

Did you try escaping the data:
my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
DBD::Pg::PG_BYTEA });

Susan

Rafal Pietrak
<rafal@zorro.isa-geek.c To: pgsql-general@postgresql.org
om> cc:
Sent by: Subject: Re: [GENERAL] UTF-8 context of BYTEA datatype??

|-------------------|
pgsql-general-owner@pos | [ ] Expand Groups |
tgresql.org |-------------------|

05/29/2006 06:10
AM

On Mon, 2006-05-29 at 14:01 +0200, Martijn van Oosterhout wrote:

How come the bytearea is *interpreted* as having encoding?

Actually, it's not the bytea type that is being interpreted, it's the
string you're sending to the server that is. Before you send bytea data
in a query string, you have to bytea encode it first. The DBD::Pg
manpage seems to suggest something like:

$rv = $sth->bind_param($param_num, $bind_value,
{ pg_type => DBD::Pg::PG_BYTEA });

Hmmm, despite initial euphoria, this doesn't actually work.

Subsequently I've also tried putting SQL_BINARY in place of that
hash-ref, and plain DBD::Pg::PG_BYTEA, and also I tried to use 'TYPE =>'
instead of pg_type. (All those hints in man DBI). None of that worked
either.

But I also did:
$db->do('SET client_encoding = LATIN1') or die "SET";
just after connect and before prepare, and this produced a slightly
different result.... no ERROR, but the image was cut short to 9-bytes
inside the database data-row.

Would perl have interpreted this command according to it's semantics?
And change it's own default string handling accordingly!?

Not knowing the internals, I wouldn't bet on whichever, but I have my
doughts - my quess is thet DBI driver doesn't go that far. So if it
hasn't interpretted the 'SET client_encodding' internally, but just
passed that to database, the only thing that changed is the database
frontend context.

So may be the original error came from the database itself anyway?

Any ideas? (still hopping I wont have to write a C-level interface
function just to test what's really happening.... :)

--
-R

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

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------

On Tue, 2006-05-30 at 09:05 -0700, SCassidy@overlandstorage.com wrote:

Did you try escaping the data:
my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
DBD::Pg::PG_BYTEA });

No. But:
$ ./test
Undefined subroutine &main::escape_bytea called at ./test line 34.

Where can I find one?
$ grep -Rl escape_bytea /usr/share/perl* /usr/lib/perl*
... returns nothing. Neither is listed among:
psql>\df
output.

Where should I look for it?

--
-R

Sorry, forgot:

sub escape_bytea {
my ($instring)=@_;
my $returnstring=join ('',map {
my $tmp=ord($_);
($tmp >= 32 and $tmp <= 126 and $tmp != 92) ? $_ :
sprintf('\%03o',$tmp);} split (//,$instring));
return $returnstring;
} # end sub escape_bytea

Rafal Pietrak
<rafal@zorro.isa-geek.c To: SCassidy@overlandstorage.com, pgsql-general@postgresql.org
om> cc:
Sent by: Subject: Re: [GENERAL] UTF-8 context of BYTEA datatype??

|-------------------|
pgsql-general-owner@pos | [ ] Expand Groups |
tgresql.org |-------------------|

05/30/2006 10:06
AM

On Tue, 2006-05-30 at 09:05 -0700, SCassidy@overlandstorage.com wrote:

Did you try escaping the data:
my $rc=$sth->bind_param(1, escape_bytea($imgdata), { pg_type =>
DBD::Pg::PG_BYTEA });

No. But:
$ ./test
Undefined subroutine &main::escape_bytea called at ./test line 34.

Where can I find one?
$ grep -Rl escape_bytea /usr/share/perl* /usr/lib/perl*
... returns nothing. Neither is listed among:
psql>\df
output.

Where should I look for it?

--
-R

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

http://www.postgresql.org/docs/faq

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------

Rafal Pietrak wrote:

On Mon, 2006-05-29 at 14:01 +0200, Martijn van Oosterhout wrote:

How come the bytearea is *interpreted* as having encoding?

Actually, it's not the bytea type that is being interpreted, it's the
string you're sending to the server that is. Before you send bytea data
in a query string, you have to bytea encode it first. The DBD::Pg
manpage seems to suggest something like:

$rv = $sth->bind_param($param_num, $bind_value,
{ pg_type => DBD::Pg::PG_BYTEA });

Hmmm, despite initial euphoria, this doesn't actually work.

Just an idea: make sure DBD::Pg::PG_BYTEA is defined.
If not, you're just lacking a "use DBD::Pg;" and the result
you describe is to be expected.

Otherwise, you could use the DBI_TRACE environment variable to learn
what the db driver is issuing to the database at the libpq level.

--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

On Tue, 2006-05-30 at 20:12 +0200, Daniel Verite wrote:

Rafal Pietrak wrote:

Hmmm, despite initial euphoria, this doesn't actually work.

Just an idea: make sure DBD::Pg::PG_BYTEA is defined.
If not, you're just lacking a "use DBD::Pg;" and the result

:) This time it's a hit. Thenx!

Now, this is probably not exactly the furum to discuss that, but:
1. I did quite a few scripts with DBI, not only for Postgesql in fact -
scripts worked flowlessly between Oracle/Sybase and the old DBASE files,
too. And I have never fell into a problem of missing the an include for
a particular driver - simple "use DBI;" did all the magic.
2. I admitt, that I should have spotted myself, that the
DBD::Pg::PG_BYTEA might not have been recognized without the use
clausure, but the driver itself understands prity much of the
underlaying datatypes - I fon't need to bind explicitly for SQL_DATE or
SQL_INTEGER. Why should I care more for binary objects?

So may be the pgtype_bytea should also be recognised? May be current
driver behavior should be regarded as a BUG?

Does anyone know if this behavior is in the driver for a reason?

--
-R