P.S. I tried changing the GetIdentity row to:

    GetIdentity=’Select max(IDENTCOL) from &TableName’

and it still doesn't work.

IMO, postgresql isn't supported very well yet in pb.

for example, PB does not support the returns table syntax in a stored function (or maybe doesn't just when there are parameters)

CREATE OR REPLACE FUNCTION customer_cdname_search(searchon text)
RETURNS TABLE(cus_cd text, cus_name text)
AS $$
SELECT customer.cus_cd, customer.cus_name
FROM customer
WHERE customer.cus_cd like UPPER(searchon)
;
$$ language 'sql';


the function runs fine in pgAdmin.

powerbuilder throws an error:
column searchon does not exist:

----------------
anyway, i gave up on postgresql until PB support has been added.
I don't have any CR number to reference, but ZhaoKai added it as a requirement.

I've tried turning on tracing, and I see SQL stuff happening in the dbtrace.log file, but even in another DB (Firebird) where the identity key stuff is working perfectly, nothing goes into the trace file for retrieving the identity key. Can anyone suggest anything else I can do to trace this better? Anyone got identity keys working in Postgres? (I don't care about returning tables from procedures, that someone else wrote about.) Or does anyone else have any killer issues with Postrgres? I was assuming it would be fine since it's supported in Appeon Web.

OK, I think I've narrowed this down to what I think is a PB bug (or at least something someone with some internals knowledge needs to tell me how to work around), and have submitted as it a bug. What helped me was turning on SQL statement logging in PostgreSQL (as suggested by someone on one of their mailing lists I posted this same question to).

The first thing I found out is that when I put the GetIdentity line in the PBODB170.INI file like this:

    GetIdentity="Select currval('GEN_&TableName')"

the double quotes were persisted into the query sent to the Postgres DB (which is not how INI files are supposed to work - they should have been trimmed). So after some experimentation to see how quotes worked in PB's INI file functions I changed that to:

    GetIdentity='Select currval('GEN_&TableName')'

Oddly enough the embedded single quotes are fine in terms of the reading from the INI file - it seems to just strip the outer quotes and leave the inner ones.

However, when I looked at the SQL logging after that, when doing a save in a DW where that should have been called, was that Postgres was receiving it as shown - the variable &TableName wasn't being translated into the actual table name the DW was acting upon. My guess is that it's because it's within single quotes, and PB's internals for dealing with GetIdentity interpreted that to mean it was a section that couldn't have variables expanded in it. The PostgreSQL syntax does require exactly single quotes there, around the generator name that generates the identity keys.

If I get a solution back to my bug report I will post it here as well. (Of course, if this further information allows someone reading this community post to give me the answer, that would be great too!)

Brad Mettee just gave me the correct answer:

GetIdentity='Select currval(''GEN_&TableName'')'

Apparently you have to double the single quotes around GEN_&TableName. If only there was some detailed documentation for the PBODB*.INI files ...