Discussion:
Questions on dynamic execution and sqlca
Bill Epstein
2014-08-06 16:28:18 UTC
Permalink
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)
David G Johnston
2014-08-06 16:45:00 UTC
Permalink
Post by Bill Epstein
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
Post by Bill Epstein
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.
Post by Bill Epstein
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.
Post by Bill Epstein
- 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.
Post by Bill Epstein
- 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.
Post by Bill Epstein
- 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.
Post by Bill Epstein
(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-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Guillaume Lelarge
2014-08-07 05:16:45 UTC
Permalink
Post by David G Johnston
Post by Bill Epstein
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
Post by David G Johnston
You likely need to use "EXECUTE" in PostgreSQL
Post by Bill Epstein
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.
Post by Bill Epstein
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
Post by David G Johnston
on how you write the function and the caller if you eventually ROLLBACK you
could lose the logging.
Post by Bill Epstein
- 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.
Post by Bill Epstein
- 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.
Post by David G Johnston
Post by Bill Epstein
- 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.
Post by Bill Epstein
(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.
--
http://postgresql.1045698.n5.nabble.com/Questions-on-dynamic-execution-and-sqlca-tp5813929p5813934.html
Post by David G Johnston
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-general
David Johnston
2014-08-07 05:24:24 UTC
Permalink
Post by David G Johnston
Post by David G Johnston
Post by Bill Epstein
- What are the differences among PL/SQL, PL/PGSQL and pgScript.
The first two are languages you write functions in. pgScript is simply
an
Post by David G Johnston
informal way to group a series of statements together and have them
execute
Post by David G Johnston
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.

David J.​
Guillaume Lelarge
2014-08-07 07:37:20 UTC
Permalink
Post by David Johnston
Post by David G Johnston
Post by David G Johnston
Post by Bill Epstein
- What are the differences among PL/SQL, PL/PGSQL and pgScript.
The first two are languages you write functions in. pgScript is simply
an
Post by David G Johnston
informal way to group a series of statements together and have them
execute
Post by David G Johnston
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.
No problem :)

pgscript reference is available on
http://pgadmin.org/docs/1.18/pgscript.html.

Note that pgScript isn't specific to pgAdmin, AFAIK.
--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com
Ray Stell
2014-08-06 17:29:59 UTC
Permalink
I'm very new to Postgres, but have plenty of experience developing stored procs in Oracle.
I found this helpful:

http://www.amazon.com/PostgreSQL-Server-Programming-Hannu-Krosing-ebook/dp/B00DMYO2D2/ref=tmm_kin_swatch_0?_encoding=UTF8&sr=8-2&qid=1407345445
Krosing, Hannu; Mlodgenski, Jim; Roybal, Kirk (2013-06-25). PostgreSQL Server Programming (Kindle Locations 272-273). Packt Publishing. Kindle Edition.
Loading...