Обсуждение: Altering a column if it exists

Thara Vadakkeveedu <tharagv@yahoo.com> writes:
> The following piece of code throws a plsql exception when run through jdbc...�
> DO $$
> ���������������
> BEGIN
> �������������������������������
> IF EXISTS (SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name
> = 'position' and column_name='org_role_id')
> �������������������������������
> THEN
> �����������������������������������������������
> ALTER TABLE POSITION RENAME COLUMN org_role_id TO job_id;
> �������������������������������
> END IF;
> END$$;

If you dig down through all the Java noise, the problem reported by the
database server is:

> org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted string

So apparently something on the client side is splitting this into more
than one command to be sent to the server; it looks like that something
thinks the first semicolon terminates the command, even though it's inside
a quoted string.  Most likely, that code doesn't understand dollar-quoting
at all.

This might be the fault of the JDBC driver, if you're using an old one;
I'm not sure when that code got taught about dollar-quoted strings.
It could be something further up the stack, though, too.

If you can't identify and fix the culprit, you could switch to using
a plain string literal for the DO (and then doubling all the quote
marks inside the literal...)

            regards, tom lane

Tom Lane-2 wrote
>> END$$;
>
> If you dig down through all the Java noise, the problem reported by the
> database server is:
>
>> org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted
>> string
>
> So apparently something on the client side is splitting this into more
> than one command to be sent to the server; it looks like that something
> thinks the first semicolon terminates the command, even though it's inside
> a quoted string.  Most likely, that code doesn't understand dollar-quoting
> at all.

More likely it is the fact that you do not have a space between "END" and
the "$$".

In certain situations there can be a problem using dollar-quoting with JDBC
- mainly in regards to JDBC-compliant escaping but I do not recall the
specifics at the moment - but a straight literal $$...$$ block works just
fine.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Altering-a-column-if-it-exists-tp5785924p5785949.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

David Johnston wrote
>
> Tom Lane-2 wrote
>>> END$$;
>>
>> If you dig down through all the Java noise, the problem reported by the
>> database server is:
>>
>>> org.postgresql.util.PSQLException: ERROR: unterminated dollar-quoted
>>> string
>>
>> So apparently something on the client side is splitting this into more
>> than one command to be sent to the server; it looks like that something
>> thinks the first semicolon terminates the command, even though it's
>> inside
>> a quoted string.  Most likely, that code doesn't understand
>> dollar-quoting
>> at all.
> More likely it is the fact that you do not have a space between "END" and
> the "$$".
>
> In certain situations there can be a problem using dollar-quoting with
> JDBC - mainly in regards to JDBC-compliant escaping but I do not recall
> the specifics at the moment - but a straight literal $$...$$ block works
> just fine.
>
> David J.

Not sure what I was thinking here but the space-issue cannot be right though
the "END" does need a semi-colon to be valid pl/pgsql.  Normally after the
semi-colon you would have a newline and then the closing dollar-quotes.
That layout is what I was looking for but mis-translated it into a
white-space error which is obviously wrong since $$somevalue$$ is indeed
valid.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Altering-a-column-if-it-exists-tp5785924p5785954.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

David Johnston <polobo@yahoo.com> writes:
> Not sure what I was thinking here but the space-issue cannot be right though
> the "END" does need a semi-colon to be valid pl/pgsql.  Normally after the
> semi-colon you would have a newline and then the closing dollar-quotes.
> That layout is what I was looking for but mis-translated it into a
> white-space error which is obviously wrong since $$somevalue$$ is indeed
> valid.

I think years ago we required a semicolon after the last END of a plpgsql
function body, but we don't anymore.  In any case, the error is being
thrown before the plpgsql parser ever gets to look at the string, so
whether that's true or not isn't the immediate problem.

At this point I'm betting the OP is using an old JDBC driver version
that doesn't understand dollar-quoted strings, or that there's some
layer above it in his client software stack that doesn't understand
them (but thinks it knows how to pull apart multi-statement SQL
strings).

A quick look into the pgsql-jdbc archives says that dollar quote parsing
was implemented in 2006, with some bug fixes (for cases this example
wouldn't hit) in late 2007.  I don't know how that relates to JDBC driver
version numbers unfortunately, but certainly anything less than about five
years old ought to be OK.

            regards, tom lane