Thread: Questions on dynamic execution and sqlca
I'm very new to Postgres, but have plenty of experience developing stored procs in Oracle.
I'm going to be creating Postgres stored procedures (functions actually, since I discovered that in postgres, everything is a function) to do a variety of batch-type processing. These functions may or may not be called by the .Net application that is being developed. To support both my Postgres function development and run-time monitoring, I wanted to develop generic logging functions that would be called by other Postgres functions to be developed in order to help trace through code and collect error information.
The attached create_log_utilities.sql holds plsql for creating two logging functions (one for logging status messages, and one for logging errors). In the log_msg function, the various sets of EXEC and EXECUTE statements are from my experimenting with dynamically generating SQL. If I could get it working, the intent is to be able to add a LogTableName as an input parameter, thereby allowing individual developers to utilize their own version of the log table (w/ the same columns). I've been able to do this sort of thing w/ Oracle before.
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 (is there a setting I need to set in order to be able to include EXEC directives?). The closest I've come is the currently uncommented prepared statement - it compiles, but I get the following error messages:
- 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
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).
To exercise the code, I'm just executing select utility.logging_test(); in a query window.
A few other items I could use clarification on:
- What's the difference between hitting the Execute Query and Execute PGScript buttons? Both seem to compile the functions.
- What are the differences among PL/SQL, PL/PGSQL and pgScript.
- 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?
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
Thanks.
Bill
_____________________
William Epstein
Consulting I/T Specialist
AIS ADM Information Management
US Federal
Office/Fax: 301-240-3887, Tie Line: 372-3887
International Business Machines (IBM) Corporation
Global Business Services (GBS)
Attachment
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.
I'm very new to Postgres, but have plenty of experience developing stored procs in Oracle.
Attachment
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
> > - 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.
> > - 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.
Yeah, I probably should have either researched the answer or just left it alone. I am not all that familiar with pgAdmin - I figured it was just a souped up script runner with maybe a couple of features like variables but otherwise allowing only SQL commands.
--