Обсуждение: error in SELECT

Hi,

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
    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;
    if (Profile_Detected = 1) then
      /* detect if service already exists in database */
      SELECT count(*)
      INTO Service_Already_Exist
      FROM immense.services
      WHERE service_nom = service_name;
      if (Service_Already_Exist = 0) then
         /* to reset AUTO_INCREMENT field : service_id */
         alter table services auto_increment=1;
        /* service does not exist, so we can add it */
        insert into immense.services
          set service_name = service_nom;
        set result = false;
        set error_message="new service created";
      else
        /* service already exists in database and can not be created */
        set result = true;
        set error_message = "service already exists";
      end if;
    else
      set result=true;
      set error_message = "user does not exist";
    end if;
END;
'

-----
What could it be ? I'm lost :-(
thanks for help.

Maileen

---

New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.

"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 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

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/
> From each according to his ability. To each according to his ability to litigate.

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

    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

On Tue, May 30, 2006 at 10:26:31PM +0200, Rafal Pietrak wrote:
> 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?

Well actually, the driver doesn't understand any datatypes at all,
that's the problem. What's happening is that to send the query to the
server, the driver has to load all your paramters into the query string
and send it. And the server has to decode it all before it's even
looked at the string so it has no idea it's a bytea.

That's why bytea need special encoding to get around this check.

However, there is a solution: send the paramters seperate from the
query. In fact, postgres has been able to do that for a while now but
not all interfaces have been made to use it. My guess is that those
other databases you've used were already doing this so didn't see the
issue. I don't know if DBD:Pg does this though, maybe it needs to be
triggered somehow.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Tue, 2006-05-30 at 22:47 +0200, Martijn van Oosterhout wrote:
> That's why bytea need special encoding to get around this check.

But may be you would know, why I should write:

    { pg_type => DBD::Pg::PG_BYTEA }

instead of possibly more generic:

    { TYPE => SQL_BINARY }

The later is *explicitly* stated as undocumented/invalid/depreciated by
the DBD::Pg documentation. While if valid, it would potencially allow me
NOT to "use DBD::Pg;" -- which would be a GoodThing(tm).

Do you know why?

> However, there is a solution: send the paramters seperate from the

How? I always thought that:
1. $db->prepare("string with q-marks in it"); $db->execute(@vector) does
the trick. (and in fact, "prepare" allows driver to sense datatypes for
@vector). I always do that, but exactly this form failed in this case.
2. or you mean $sh->bind_param(id, $value, \%atr), which I've just
learned to use? This one can potencially be generaly usefull if
available in the form "TYPE => SQL_BINARY", instead of "pg_type =>
DBD::Pg::PG_BYTEA".
3. or you mean something else is there. What is it? In what other way I
can pass parameters separately?

--
-R

    Martijn van Oosterhout wrote:

> However, there is a solution: send the paramters seperate from the
> query. In fact, postgres has been able to do that for a while now but
> not all interfaces have been made to use it. My guess is that those
> other databases you've used were already doing this so didn't see the
> issue. I don't know if DBD:Pg does this though, maybe it needs to be
> triggered somehow.

Actually, this is the case already. When a parameter is explicitly
typed as bytea, then libpq's PQexecPrepared is used and the binary
contents are passed outside of the query.

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

On Wed, May 31, 2006 at 11:31:28AM +0200, Daniel Verite wrote:
>     Martijn van Oosterhout wrote:
>
> > However, there is a solution: send the paramters seperate from the
> > query. In fact, postgres has been able to do that for a while now but
> > not all interfaces have been made to use it. My guess is that those
> > other databases you've used were already doing this so didn't see the
> > issue. I don't know if DBD:Pg does this though, maybe it needs to be
> > triggered somehow.
>
> Actually, this is the case already. When a parameter is explicitly
> typed as bytea, then libpq's PQexecPrepared is used and the binary
> contents are passed outside of the query.

Why isn't PQexecPrepared always used? And why does typing it SQL_BINARY
not do the same?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Rafal Pietrak asked:

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

SQL_INTEGER and SQL_DATE are generic data types, and come from DBI.
DBD::Pg::PG_BYTEA, as you might guess from the name, comes from
DBD::Pg. However, *all* of them have to be loaded explicitly - this
is by design. For example, try running this script:

#!perl

package testone;
use DBI;

printf "SQL_INTEGER is %d\n", SQL_INTEGER;

package testtwo;
use DBI qw(:sql_types);

printf "SQL_INTEGER is %d\n", SQL_INTEGER;


If you are asking why the bind has to happen at all, it is partly because
libpq does not support returning the data types yet, and partly because
unlike most other data types, it is very important that DBD::Pg (and libpq,
and the backend) be told explicitly that a binary string is being used,
so that the length can be sent, as a null character may not represent the
end of the string.


Martijn van Oosterhout asked:

> Actually, this is the case already. When a parameter is explicitly
> typed as bytea, then libpq's PQexecPrepared is used and the binary
> contents are passed outside of the query.
>
> Why isn't PQexecPrepared always used? And why does typing it
> SQL_BINARY not do the same?

SQL_BINARY is not the same as PG_BYTEA - we don't necessarily handle binary
strings the same way as other databases. Still, it may be worth revisiting
if we can do something a little more intuitive, so I'll revisit this.

As far as PQexecPrepared, there are many reasons for not using it - insufficient
PG version, using DDL, having a DEFAULT in your query, etc. But in general,
DBD::Pg does its best to use it (and PQexecParams) whenever possible.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200605312152
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFEfknRvJuQZxSWSsgRAt33AJ9NS2LFwSyoYSHgEjLbYUSCvr/kTQCfXUPR
v/RpO73hIhY7CwXVcaqwCKI=
=zXCq
-----END PGP SIGNATURE-----

On Thu, 2006-06-01 at 02:00 +0000, Greg Sabino Mullane wrote:
> #!perl
>
> package testone;
> use DBI;
>
> printf "SQL_INTEGER is %d\n", SQL_INTEGER;
>
> package testtwo;
> use DBI qw(:sql_types);
>
> printf "SQL_INTEGER is %d\n", SQL_INTEGER;

But this is not as bad as having to "use DBD:Pg" (or any other dviver
speciffic include).

> unlike most other data types, it is very important that DBD::Pg (and libpq,
> and the backend) be told explicitly that a binary string is being used,
> so that the length can be sent, as a null character may not represent the
> end of the string.

Well, for a humble utility programmer like myself - not really knowing
the internals - it's *very* desirable to be able to just "CREATE TABLE"
with 'binary' column, and as a result, have the client library know
that, and act on provided data accordingly.

The most desirable state is when my script works equally well with any
driver - like in case, when the sriver is selected on command line (and
I don't really mean here "eval 'require $ARGV[0]'" :).

> Martijn van Oosterhout asked:
> >
> > Why isn't PQexecPrepared always used? And why does typing it
> > SQL_BINARY not do the same?
>
> SQL_BINARY is not the same as PG_BYTEA - we don't necessarily handle binary
> strings the same way as other databases. Still, it may be worth revisiting

This is something I don't understand.

As a programmer, I have *chosen* the PG_BYTEA (or to be precise: I've
chosen to: "CREATE TABLE test (img BYTEA)"), just to have the
functionality of a binary opoque value - not interpretted in any way by
the RDBMS (like: not converted according to clinet_encoding).

In my opinion I meant SQL_BINARY.

So if in the postresql RDMBS, there is no other datatype closer to the
SQL_BINARY semantics, the PG_BYTEA should be just a synonym.

--
Rafal Pietrak <rafal@poczta.homelinux.com>