Re: :PgSQL: More Queestions

> >> Maybe it's just too complex, because, looking at DBD::ODBC's
> >> dbd_preparse(), the handling of literals in the query seems a good
> >> deal
> >> more straight-forward (though it doesn't appear to handle '\'' or "\""
> >> -- am I reading that right?
> >
> > Nope, it handles " or '.
> >
> > if (*src == '"' || *src == '\'') {
> > etc...
> > }
>
> It doesn't appear to handle "...""...", though, right? Or am I missing
> it?

Actually, it does, but completely "accidentally". DBD::ODBC is not
concerned with the fact that there are " inside the ", so:
the first quote sets in_literal=1 (true)
the second quote sets in_literal=0 (false)
the third puts us right back in_literal=1, so we can keep processing.
Since there should be an even number of " marks, I think we're OK here.

Seems too simple, but, it works. There may be a case that gets me, but I
don't think so.

>
> > My advice: if you don't have to support multiple result sets, do it
> once per
> > execute. If you setup that "flag" to avoid re-doing work and find
> > that you
> > need to support multiple-result sets, you can always clear the flag...
>
> I'll have to check with the PostgreSQL folks on this.
>
> PostgreSQL folks, can the same statement return a different number of
> fields on different executes? I'm guessing yes for something like this,
> though:
>
> CREATE TABLE foo ( bar int, bat, text);
>
> SELECT * FROM foo; -- Returns two fields.
>
> ALTER TABLE foo ADD COLUMN fat int;
>
> SELECT * FROM foo; -- Returns three fields.

You probably wouldn't have that prepared as one statement.
Using, say, SQL Server, you can do something like (pseudo code here with
some exaggeration, but I can actually provide test code that someone sent):

sp_pain_to_deal_with(int i) as

if (i == 1)
select a, b, c from foo; /* returns a, b, c as result set */
elseif (i == 2)
delete from foo; /* returns a count, not a result set */
else
select d, a, b, f from foo; /* returns something completely different */
end;

$sth = $dbh->prepare({ call sp_pain_to_deal_with(?)});
$sth->execute(1);
$sth->execute(2);
$sth->execute(3);

all return different result sets.

Or, the more "normal" case:
$sth = $dbh->prepare("insert a, b, c into foo values (?, ?, ?); select
@@identidy;");
which, in one "shot" insert into the table and gets back the
auto-incremented id for the table (again, there is probably a lot of syntax
issue with the above, but the concept is there).

Regards,

Jeff