Re: Questions on dynamic execution and sqlca

Bill Epstein wrote
> I've tried a variety of ways based on the on-line docs I've seen, but I
> always get a syntax error on EXEC when I use only the line EXEC  statement

You likely need to use "EXECUTE" in PostgreSQL


>    INFO:  INSERT INTO UTILITY.BPC_AUDIT (COMPONENT, ACTIVITY, AUDIT_LEVEL,
>    AUDIT_TIME, NOTE, SQL) VALUES ('Overpayment','Create
>    TLI','LOG','2014-08-06 10:44:23.933','Created TLI','INSERT INTO
>    TLIA...')
>    CONTEXT:  SQL statement "SELECT utility.LOG_MSG (p_log_yn, p_component,
>    p_function, p_note, p_sql)"
>    PL/pgSQL function utility.logging_test() line 24 at PERFORM
>    ERROR:  INSERT has more expressions than target columns
>    LINE 3:          VALUES ($1, $2, $3, $4, $5, $6)
>                                                 ^
>    QUERY:  PREPARE myinsert7 (text, text, text, timestamp, text, text) AS
>             INSERT INTO UTILITY.BPC_AUDIT (COMPONENT, ACTIVITY,
>    AUDIT_LEVEL, NOTE, SQL)
>             VALUES ($1, $2, $3, $4, $5, $6)
>    CONTEXT:  PL/pgSQL function utility.log_msg
>    (character,text,text,text,text) line 48 at SQL statement
>    SQL statement "SELECT utility.LOG_MSG (p_log_yn, p_component,
>    p_function, p_note, p_sql)"
>    PL/pgSQL function utility.logging_test() line 24 at PERFORM
>    ********** Error **********
>
>    ERROR: INSERT has more expressions than target columns
>    SQL state: 42601
>    Context: PL/pgSQL function utility.log_msg
>    (character,text,text,text,text) line 48 at SQL statement
>    SQL statement "SELECT utility.LOG_MSG (p_log_yn, p_component,
>    p_function, p_note, p_sql)"
>    PL/pgSQL function utility.logging_test() line 24 at PERFORM

Since "COMPONENT, ACTIVITY, AUDIT_LEVEL, NOTE, SQL" is only 5 columns and
you are sending 6 it is not surprising that you are getting an error.


> In the other function (log_error ), the problem I'm having is that I'm
> trying to pull out the sqlca error code and description (as I've done in
> the past w/ Oracle), in order to write that information in my log table.
> The intent is that this function will only be called from within an
> EXCEPTION block (as I do in my logging_test  function - I purposely run a
> bad query to trigger it).

You still have to deal with the fact that PostgreSQL functions operate in
the transaction context of the caller; they cannot set their own.  Depending
on how you write the function and the caller if you eventually ROLLBACK you
could lose the logging.


> - What's the difference between hitting the Execute Query and Execute
> PGScript buttons?  Both seem to compile the functions.

Execute Query just sends the statement(s) to the server
Execute PGScript wraps the statements in a transaction so that either they
are succeed or all fail.
Basically with Execute Query if a statement in the middle fails everything
before it still commits (auto-commit)

For a single statement there is no difference.

> - What are the differences among PL/SQL,  PL/PGSQL and pgScript.

The first two are languages you write functions in.  pgScript is simply an
informal way to group a series of statements together and have them execute
within a transaction.


> - I installed Postgres 9.3.4 and  I'm using PEM v4.0.2.  When I click on
> the icon to "Execute arbitrary SQL queries", I notice that the icons on
> the
> window that opens are different from the pgAdmin PostgreSQL Tools window
> that opens if I double-click on one of my .sql files.  Is there a
> difference in these tools?

No idea - but probably.  But there are likely many similarities too.


> Attached are the relevant scripts:
> (See attached file: create_bpc_audit.sql) - Create the log table
> (See attached file: create_log_utilities.sql)- Code to create the two
> logging functions
> (See attached file: test_log_utilities.sql)- Code to exercise the msg and
> error logging functions

Didn't even open these...


David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Questions-on-dynamic-execution-and-sqlca-tp5813929p5813934.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.