Accessing composite type elements
Hi All,
I’m not sure if this is the actual place for this but I guess I can start here.
The question I have is, I’ve created a composite type like this:
CREATE TYPE myxml AS { encoding_ int4, xml_ xml };
In my client-side C code I am using PQgetvalue to pull in the data from the
row/column. However, since it is a composite type, it is unclear to me how to
get the individual members from the data. I have googled but I probably just am
not googling the correct term because I cannot find any examples of this being
done anywhere. A hex dump of the data gives me this:
0x0002001700046f01008e01433c637573746f6d6572696e666f20786d6c6e733d22687474703a2f2f6c7a6c6162732e637573742e636f6d22204369643d22543130303130303031223e3c6e616d653e5461626c6520584d4c…
I can tell that the green portion is my endcoding_ value and the blue section
is the xml_ data.
My best guess right now is:
* 0x2 is the number of members
* 0x4 and 0x143 are the lengths of the individual members
* 0x17 and 0x8e are the OID for the member type
Is this the proper layout of these composite types? Can I go ahead and use this
without possibly having it broken in the future? Are there any existing
supported APIs that I can use instead to get this information?
Regards
G
Re: [EXT] Re: Accessing composite type elements
> Binary representations are not too well documented :-(. However, > looking at record_send() helps here. will have a look… > Right, with the additional proviso that -1 "length" indicates a null > field value. Thx, Tom… never thought the null field… -- Regards, Garfield A. Lewis
Re: [EXT] Re: Accessing composite type elements
> take a look at libpqtypes. it's client side extension library to libpq that > implements the binary protocol. > > https://github.com/pgagarinov/libpqtypes > > merlin Thx, Merlin… I’ll have a look see…
SCROLLABLE/UPDATABLE cursor question
Hi All, I’m not sure where to ask the question so I’ll start here. Does anyone know if Postgres has any plans to support statements like FETCH/MOVE in the non-forward direction for SCROLLABLE/UPDATABLE cursors? -- Regards, Garfield A. Lewis
Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question
Tom Lane writes: > Doesn't that work already? Hi Tom, This works perfectly well for a NON-UPDATABLE cursor: [lzsystem@nucky LZRDB-5220] $ psql -U lzpgsupr -d wdbs -f curs.pgs -e BEGIN; BEGIN CREATE TABLE t0(c0 int); psql:curs.pgs:2: NOTICE: DDL was performed without updating catalog tables: Note that CREATE TABLE from a non-SDM client does not maintain LzRelational catalog tables CREATE TABLE INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); INSERT 0 16 DECLARE cur0 SCROLL CURSOR FOR SELECT * FROM t0; DECLARE CURSOR MOVE FORWARD 10 IN cur0; MOVE 10 MOVE FORWARD -3 IN cur0; MOVE 3 MOVE BACKWARD 3 IN cur0; MOVE 3 FETCH PRIOR FROM cur0; c0 2 (1 row) ROLLBACK; ROLLBACK However, adding FOR UPDATE gets me this: [lzsystem@nucky LZRDB-5220] $ psql -U lzpgsupr -d wdbs -f curs.pgs -e BEGIN; BEGIN CREATE TABLE t0(c0 int); CREATE TABLE INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); INSERT 0 16 DECLARE cur0 /*SCROLL*/ CURSOR FOR SELECT * FROM t0 FOR UPDATE; DECLARE CURSOR MOVE FORWARD 10 IN cur0; MOVE 10 MOVE FORWARD -3 IN cur0; psql:curs.pgs:7: ERROR: cursor can only scan forward HINT: Declare it with SCROLL option to enable backward scan. MOVE BACKWARD 3 IN cur0; psql:curs.pgs:8: ERROR: current transaction is aborted, commands ignored until end of transaction block FETCH PRIOR FROM cur0; psql:curs.pgs:9: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK; ROLLBACK In fact, adding both SCROLL and FOR UPDATE specifically says they are not compatible: [lzsystem@nucky LZRDB-5220] $ psql -U lzpgsupr -d wdbs -f curs.pgs -e BEGIN; BEGIN CREATE TABLE t0(c0 int); INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15); INSERT 0 16 DECLARE cur0 SCROLL CURSOR FOR SELECT * FROM t0 FOR UPDATE; psql:curs.pgs:4: ERROR: DECLARE SCROLL CURSOR ... FOR UPDATE is not supported DETAIL: Scrollable cursors must be READ ONLY. MOVE FORWARD 10 IN cur0; psql:curs.pgs:6: ERROR: current transaction is aborted, commands ignored until end of transaction block MOVE FORWARD -3 IN cur0; psql:curs.pgs:7: ERROR: current transaction is aborted, commands ignored until end of transaction block MOVE BACKWARD 3 IN cur0; psql:curs.pgs:8: ERROR: current transaction is aborted, commands ignored until end of transaction block FETCH PRIOR FROM cur0; psql:curs.pgs:9: ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK; ROLLBACK We are running Postgres 14: [sysprog@nucky workspace] (h-master-LZRDB-5220-fix-WCOC-failure)*$ psql -V psql (PostgreSQL) 14.7 Is this allowed maybe in Postgres 15?
Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question
> Adrian Klaver mailto:adrian.kla...@aklaver.com>> > wrote: > This: > psql:curs.pgs:2: NOTICE: DDL was performed without updating catalog > tables: Note that CREATE TABLE from a non-SDM client does not maintain > LzRelational catalog tables > seems to indicate you are using some sort of Postgres fork. > > Is that the case and if so what is the fork? This is not a fork… it is pure Postgres 14 with an extension that checks for a certain environment and reports a NOTICE otherwise. -- Regards, Garfield A. Lewis
Re: [EXT] Re: SCROLLABLE/UPDATABLE cursor question
> Garfield Lewis mailto:garfield.le...@lzlabs.com>> > wrote: > This is not a fork… it is pure Postgres 14 with an extension that checks for > a certain environment and reports a NOTICE otherwise. Oops, said extension meant trigger… -- Regards, Garfield A. Lewis
Re: [EXT] Re: [Beginner Question] How to print the call link graph?
> If no one here can, then superuser.com, unix.stackexchange.com or > stackoverflow.com should be able to. (Just don't cross-post...) If I understand the question this is a GDB question, correct? If so, I would simply set a breakpoint in GDB at that function like so: b SocketBackend commands bt 2 end this will break then print a backtrace of the last 2 functions (you can remove the 2 to get a full backtrace or change it to some other value). You could also add a continue (c) after the bt to have it run and just print out the backtrace until all is done (save the result to a file using the set logging command) -- Regards, Garfield A. Lewis
How to tell which statement is being executed
Hi All, If I create a C function, is there a way from within that function for me to: 1. know whether it is being triggered by an INSERT or UPDATE statement * I’ve done some digging and it seems you can get this information if you provide a Planner hook, however, I need to know this much later in my function not in the Planner * Is there some way for me to get to the PlannedStmt (or anything else that may have the command type) from within my function? 2. column attribute (specifically the TYPMOD) for the affected column BTW, is there a more appropriate Postgres list/group/blog that is specifically dedicated to these types of programming questions or is it ok to post this here? Regards, Garfield
Re: [EXT] Re: How to tell which statement is being executed
> It might not be any of those. But if what you want is the most closely > nested SQL action, inspecting the ActivePortal might help (see > function_parse_error_transpose, which I think is the only in-core user). Thx, Tom… will see if this ActivePortal helps… > No. How would you even define "affected column"? Not sure I just assumed that the attributes for column being processed would be saved somewhere in some structure somewhere. After all, at some point if for instance I created a CHAR(10) column and attempted to insert a CHAR(15) that is returned by my function then Postgres will need to generate a truncation warning so it must know the TYPMOD of the column to do so. Regards Garfield From: Tom Lane Date: Friday, November 3, 2023 at 12:08 PM To: Garfield Lewis Cc: pgsql-generallists.postgresql.org Subject: [EXT] Re: How to tell which statement is being executed Garfield Lewis writes: > If I create a C function, is there a way from within that function for me to: > 1. know whether it is being triggered by an INSERT or UPDATE statement It might not be any of those. But if what you want is the most closely nested SQL action, inspecting the ActivePortal might help (see function_parse_error_transpose, which I think is the only in-core user). > 2. column attribute (specifically the TYPMOD) for the affected column No. How would you even define "affected column"? regards, tom lane
Re: Currently running queries with actual arguments?
The way I normally get this info is by setting the following: log_statement = 'all' then the arguments will be printed in the postgres.log file. There could be some other way but that is what I know. -- Regards, Garfield On 2021-09-23, 11:33 AM, "rihad" wrote: Hi, is it possible to view the list of currently running queries with $1, $2 etc replaced with the actual arguments?
Using XMLNAMESPACES with XMLEMENT
Hi All,
I am attempting to port the following statement from DB2z to Postgres:
SELECT e.empno, e.firstnme, e.lastname,
XMLELEMENT ( NAME "foo:Emp",
XMLNAMESPACES('http://www.foo.com' AS "foo"),
XMLATTRIBUTES(e.empno as "serial"),
e.firstnme,
e.lastname ) AS "Result"
FROM EMP e
WHERE e.edlevel = 12;
The NAMESPACES function is not supported by Postgres in the XMLELEMENT
function. Is there any way to get this to work? I’ve looked at the WITH syntax
but it doesn’t look like that will be helpful here.
Regards,
Garfield
Re: Using XMLNAMESPACES with XMLEMENT
I’m using PG v12.6 and no I haven’t tried the path function. Do you have an
example?
--
Regards,
Garfield A. Lewis
From: Rob Sargent
Date: Friday, September 24, 2021 at 6:52 PM
To: Garfield Lewis
Cc: "pgsql-gene...@postgresql.org"
Subject: Re: Using XMLNAMESPACES with XMLEMENT
On Sep 24, 2021, at 3:44 PM, Garfield Lewis wrote:
Hi All,
I am attempting to port the following statement from DB2z to Postgres:
SELECT e.empno, e.firstnme, e.lastname,
XMLELEMENT ( NAME "foo:Emp",
XMLNAMESPACES('http://www.foo.com' AS "foo"),
XMLATTRIBUTES(e.empno as "serial"),
e.firstnme,
e.lastname ) AS "Result"
FROM EMP e
WHERE e.edlevel = 12;
The NAMESPACES function is not supported by Postgres in the XMLELEMENT
function. Is there any way to get this to work? I’ve looked at the WITH syntax
but it doesn’t look like that will be helpful here.
Which Postgres version? Have you tried path function?
Regards,
Garfield
Re: Using XMLNAMESPACES with XMLEMENT
Thx @Pavel Stehule<mailto:pavel.steh...@gmail.com>, I’ll see if I can figure
this out… ☺
Regards,
Garfield
From: Pavel Stehule
Date: Friday, September 24, 2021 at 11:33 PM
To: Garfield Lewis
Cc: "pgsql-gene...@postgresql.org"
Subject: Re: Using XMLNAMESPACES with XMLEMENT
Hi
pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis
mailto:garfield.le...@lzlabs.com>> napsal:
Hi All,
I am attempting to port the following statement from DB2z to Postgres:
SELECT e.empno, e.firstnme, e.lastname,
XMLELEMENT ( NAME "foo:Emp",
XMLNAMESPACES('http://www.foo.com' AS "foo"),
XMLATTRIBUTES(e.empno as "serial"),
e.firstnme,
e.lastname ) AS "Result"
FROM EMP e
WHERE e.edlevel = 12;
The NAMESPACES function is not supported by Postgres in the XMLELEMENT
function. Is there any way to get this to work? I’ve looked at the WITH syntax
but it doesn’t look like that will be helpful here.
I am afraid this is not supported in Postgres. The XMLNAMESPACE clause can be
used only in XMLTABLE function. You need to make XML and in the next step you
need to modify it as string with string operation.
It can be an interesting feature, and if it is supported by libxml2, then it
can be easily implemented. But at this moment it is unsupported, and you have
to use string operations - it should not be hard to use regexp.
Regards
Pavel
Regards,
Garfield
XQuery/XPath 2.0+ support
Hi All, I am investigating whether there are any plans to have XPath 2.0 support for Postgres XML natively or are there any available extension packages that includes this support? -- Regards, Garfield A. Lewis
Re: [EXT] Re: XQuery/XPath 2.0+ support
Thx, Tom...
But isn't the libxml2 library not sufficient for this purpose? Note that I have
not tried it yet, I am still just investigating possible solutions.
--
Regards,
Garfield A. Lewis
On 2021-11-29, 12:08 PM, "Tom Lane" wrote:
Garfield Lewis writes:
> I am investigating whether there are any plans to have XPath 2.0 support
for Postgres XML natively or are there any available extension packages that
includes this support?
I don't know of anyone actively working on that. The difficulty is
that we don't especially want to write/maintain such logic ourselves,
but there don't seem to be any suitable libraries available that
(a) can be called from C and (b) have a compatible license.
regards, tom lane
Can we get the CTID value
Hi, I am creating a new type and would like to know if it was possible to access the CTID for the row affected by the INPUT and RECEIVE functions of the new type? Actually, would it be possible from the OUTPUT and SEND functions as well? Regards, Garfield
Re: [EXT] Re: Can we get the CTID value
Hi Laurenz, I need the page and possibly row of the data location to be stored as an element of the new type. This is to simulate a structure from another database system. Regards, Garfield
Re: [EXT] Re: Can we get the CTID value
A CTID is a special column documented here: https://www.postgresql.org/docs/12/ddl-system-columns.html Regards, Garfield
Re: [EXT] Re: Can we get the CTID value
> On 2022-01-20, 12:52 PM, "Tom Lane" wrote: > >Garfield Lewis writes: >> I need the page and possibly row of the data location to be stored as an > element of the new type. This is to simulate a structure from another > database system. > >You need to rethink. The datatype input function cannot know even that >the value is going to be stored anywhere, let alone exactly where. >Moreover, what would happen if the row is moved somewhere else due >to an update of some other column? > >You might be able to build something for cross-linking by putting >the logic in AFTER INSERT/UPDATE/DELETE triggers, but I think a >custom datatype is not going to be helpful for that. > > regards, tom lane Thx, Tom... I think you are right in the case of INPUT/RECEIVE, however we should be able to get that info during OUTPUT/SEND (I think) since it is fixed at that point. At the time I return the information to the user I could augment the output to add that information to the output. However, I still don't know if it is even possible to get that information in those functions. Is that at all possible? Regards, Garfield
Re: [EXT] Re: Can we get the CTID value
On 2022-01-20, 1:11 PM, "Tom Lane" wrote: >No, it's the same problem in reverse: the output function cannot >know where the value came from. There is no hard and fast >reason that it must have come out of a table, either. Consider >something as simple as > > SELECT 'blah blah'::yourtype; > >This'll invoke the type's input function to parse the literal string, >and later it'll invoke the output function to reconstruct a string > >to send to the client, and there's no table involved. > > regards, tom lane Understood, however, my last question/comment would be shouldn't the example above just result in a CTID something like (x,y) where x and y are some known UNKNOWN/INVALID values or something else representing the fact that there is no current CTID associated with the element? Basically, what I am saying is shouldn't any search for a CTID in the case just return some value to indicate the CTID doesn't exist or is UNKNOWN/INVALID? The following knows there is no CTID so shouldn’t I be able to get something similar programmatically? [sysprog@nucky lz_pgmod] (h-master-LZRDB-4714)*$ psql -U postgres -d postgres -c "select ctid, 'test'" ERROR: column "ctid" does not exist LINE 1: select ctid, 'test' ^ Regards, Garfield
Re: [EXT] Re: Can we get the CTID value
>As I said, that is impossible. > >Again, describe with many, many words what you are trying to achieve. >There is probably a solution for the underlying problem. > >Yours, >Laurenz Albe >-- >Cybertec | https://www.cybertec-postgresql.com Thanks all, for the responses... I think I just give up on this and think of something else... Regards, Garfield
Passing XML column in an array
Hi All,
I have the following code:
141 if ( 0 != iXmlArrDim ) {
142 Datum*pXmlData = NULL;
143 bool *pXmlDataNulls = NULL;
144 uint32_t iXmlCount = 0;
145 bool bFirstTime= true;
146
147 Assert( XMLOID == ARR_ELEMENTS( pXmlDataArr ) );
148
149 deconstruct_array( pXmlDataArr,
150XMLOID,
151-1,
152false,
153'i',
154&pXmlData,
155&pXmlDataNulls,
156(int32_t*)&iXmlCount );
157
158 initStringInfo( &xmlStr );
159
160 for ( size_t ix = 0; ix < iXmlCount; ++ix ) {
161 xmltype *pX= DatumGetXmlP( pXmlData[ix] );
162 char *pData = VARDATA( pX );
163 uint32_t iData = VARSIZE( pX ) - VARHDRSZ;
164
165 if ( !bFirstTime )
166 appendStringInfoString( &xmlStr, ", " );
167 else
168 bFirstTime = false;
169
170 appendStringInfoString( &xmlStr,
171 (const char*)lzXmlCharStrndup( pData,
172iData )
);
173 }
174
175 pfree( pXmlData );
176 pfree( pXmlDataNulls );
177 }
I am trying to pass an array into a new function I am creating called
lzxmlexists via the following statement:
wdbs=# SELECT "XT"."ROWKEY" , "XT"."XMLT1C1" FROM "LZQAAS"."T642_XML_T1" "XT"
WHERE "ROWKEY"=64201 AND lzxmlexists( ' $XTX//DeptName[ . = $Dn]',
'DEFAULT', ARRAY["XT"."XMLT1C1"] ) ORDER BY "ROWKEY";
The column XT.XMLT1C1 exists because I can do a simple select to see the
contents. The problem is that in the code above I hit the following error at
line 161.
160 for ( size_t ix = 0; ix < iXmlCount; ++ix ) {
(gdb)
163 xmltype *pX= DatumGetXmlP( pXmlData[ix] );
(gdb)
Program received signal SIGSEGV, Segmentation fault.
0x008b3514 in pg_detoast_datum ()
Obviously, I’ve done something wrong, or I am misunderstanding something.
BTW, if I change the array input to something like this
ARRAY[‘something-variable’::xml] it works no problem, but it seems as though
getting the input from the column results in garbage that traps.
Any suggestion/help would be greatly appreciated.
Regards,
Garfield
Re: [EXT] Re: Passing XML column in an array
>The most obvious theory is that there are some nulls in the XT.XMLT1C1 >column. Since your code isn't bothering to check pXmlDataNulls[ix], >it would hit a null-pointer dereference when accessing pXmlData[ix]. > > regards, tom lane Thx, Tom... my bad... Regards, Garfield
Get CTID from within an OUTPUT/SEND function
I would like to know if it is possible to get the CTID from within the OUTPUT and SEND functions of a CREATE TYPE? I’ve found a function called currtid_byrelname and the comment in the function prolog says it is to get a CTID but I cannot find any example usage of it. Regards, Garfield
Re: [EXT] Re: Get CTID from within an OUTPUT/SEND function
Garfield Lewis writes: > > I would like to know if it is possible to get the CTID from within > > the OUTPUT and SEND functions of a CREATE TYPE? > > No. A datatype output function has no reason to expect that the value > it's handed has ever been in a table at all. > > regards, tom lane Thx, Tom
