Re: Questions on dynamic execution and sqlca - Mailing list pgsql-general

Le 6 août 2014 18:47, "David G Johnston" <david.g.johnston@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general