Hi,

I am looking for a way to get the database messages and sqlstate
in pltcl, and I can't find it in the docs.

Must be something like "pg_result $db -status" in pgtcl,
or $stmt->errorInfo() in PHP.

Is this information somewhere in the system tables?

/Str.

Am 18.02.2011 11:29, schrieb M. Strobel:
so nothing in the system catalogs?

M. Strobel wrote:
It cannot be in the system catalogs because it is not persistent.

Unfortunately I know nothing about Tcl, but the manual says
(for spi_exec): "An error in the command causes an error to be raised."

So I guess you should catch that error and examine the information
therein. It should contain the message, and maybe it also contains the
SQLSTATE.

Yours,
Laurenz Albe

Am 03.05.2011 09:20, schrieb Laurenz Albe:
Yes, I can catch the error in pltcl, something I forgot about.
And I get the error message, but I can't get the sqlstate. So I
would have to search the message with a regex, something I
avoided so far (and forgot about it).

To be more precise, and for the benefit of message archivers,
here the test session in psql (sorry, the db is set to german):

/Str.
----------------------------------------------------

-- Test table:
trans1=> create table test_duplicates (id integer primary key,
val text);
HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen
Index »test_duplicates_pkey« für Tabelle »test_duplicates«
CREATE TABLE

-- test data:
trans1=> insert into test_duplicates values(1,'Value 1');
INSERT 0 1

-- test proc tries to do insert (wrapped lines on paste)
trans1=> create or replace function findsqlstate() returns text
as $_$
trans1$> set sql "insert into test_duplicates values
(1,'Value extra')"
trans1$> if {[catch {spi_exec $sql} catchres]} {
trans1$> return "CATCH: $catchres\n"
trans1$> } else {
trans1$> return $catchres
trans1$> }
trans1$> $_$ language pltcl;
CREATE FUNCTION

-- execute test proc
trans1=> select findsqlstate();
findsqlstate
----------------------------------------------------------------------------------
CATCH: doppelter Schlüsselwert verletzt Unique-Constraint
»test_duplicates_pkey«+

(1 Zeile)
--
-- result: error can be catched, with message
--

-- now check global variable with suspicious name
-- (this proc executes the text parameter as code)
-- (source not shown)
trans1=> select eval_me('set ::errorInfo');
eval_me
---------------------------------------------------------------------------
doppelter Schlüsselwert verletzt Unique-Constraint
»test_duplicates_pkey«+
while executing
+
"spi_exec $sql"
(1 Zeile)

trans1=> select eval_me('set ::errorCode');
eval_me
---------
NONE
(1 Zeile)

--
-- result: sqlstate or errorCode not available
-- text message available