Re: Questions on dynamic execution and sqlca

Le 6 août 2014 18:47, "David G Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> a
écrit :
>
> 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.
>

AFAICT, this isn't true. Pgscript is a client specific language. There is a
whole description of what it can do in pgadmin's manual. This was
interesting when PostgreSQL didn't have the DO statement. Now that we do,
it's rather pointless.

> > - 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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general