Thread: How to get Relation tuples in C function

How to get Relation tuples in C function

From
Patrick Handja
Date:
Greetings!

I would like to know if there is a better way to pass a relation or if the relation name (CString) as a parameter in a C function and thus be able to manipulate its tuples. The documentation is available here: https://www.postgresql.org/docs/13/xfunc-c.html#id-1.8.3.13.11. But it is not quite clear enough on how to retrieve tuples. The handling of these is quite clear. The only function I'm currently using (but not working) is the TupleDesc TypeGetTupleDesc (Oid typeoid, List * colaliases) function. Do we have a function like TupleDesc RelationNameGetTupleDesc (const char * relname) (Old and deprecated)?

regards,


Andjasubu Bungama, Patrick 

Re: How to get Relation tuples in C function

From
Tom Lane
Date:
Patrick Handja <patrick.bungama@gmail.com> writes:
> I would like to know if there is a better way to pass a relation or if the
> relation name (CString) as a parameter in a C function and thus be able to
> manipulate its tuples. The documentation is available here:
> https://www.postgresql.org/docs/13/xfunc-c.html#id-1.8.3.13.11. But it is
> not quite clear enough on how to retrieve tuples.

The thing I'd recommend you do is use SPI [1], which lets you execute
SQL queries from inside a C function.  If you don't want to do that
for whatever reason, you need to open the relation, set up a scan,
and fetch tuples from the scan, relying on low-level APIs that tend
to change from version to version.  contrib/pageinspect or
contrib/pgstattuple might offer usable sample code, although with any
prototype you might look at, it's going to be hard to see the forest
for the trees.

            regards, tom lane

[1] https://www.postgresql.org/docs/current/spi.html



Re: How to get Relation tuples in C function

From
Andy Fan
Date:


On Sun, Feb 14, 2021 at 5:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Patrick Handja <patrick.bungama@gmail.com> writes:
> I would like to know if there is a better way to pass a relation or if the
> relation name (CString) as a parameter in a C function and thus be able to
> manipulate its tuples. The documentation is available here:
> https://www.postgresql.org/docs/13/xfunc-c.html#id-1.8.3.13.11. But it is
> not quite clear enough on how to retrieve tuples.

The thing I'd recommend you do is use SPI [1], which lets you execute
SQL queries from inside a C function.  If you don't want to do that
for whatever reason, you need to open the relation, set up a scan,
and fetch tuples from the scan, relying on low-level APIs that tend
to change from version to version.  contrib/pageinspect or
contrib/pgstattuple might offer usable sample code, although with any
prototype you might look at, it's going to be hard to see the forest
for the trees.

                        regards, tom lane

[1] https://www.postgresql.org/docs/current/spi.html


Thank you tom for the reply.  What would be the difference between the
SPI and "write a pure SQL UDF" and call it with DirectFunctionCall1? I
just ran into a similar situation some days before.   Currently I think 
DirectFunctionCall1 doesn't need to maintain a connection but SPI has to
do that.


--
Best Regards

Re: How to get Relation tuples in C function

From
Michael Paquier
Date:
On Sun, Feb 14, 2021 at 09:29:08AM +0800, Andy Fan wrote:
> Thank you tom for the reply.  What would be the difference between the
> SPI and "write a pure SQL UDF" and call it with DirectFunctionCall1? I
> just ran into a similar situation some days before.   Currently I think
> DirectFunctionCall1 doesn't need to maintain a connection but SPI has to
> do that.

Hard to say without knowing your use case.  A PL function is more
simple to maintain than a C function, though usually less performant
from the pure point of view of its operations.  A SQL function could
finish by being inlined, allowing the planner to apply optimizations
as it would know the function body.  Going with SPI has the advantage
to have code able to work without any changes across major versions,
which is a no-brainer when it comes to long-term maintenance.
--
Michael

Attachment

Re: How to get Relation tuples in C function

From
Andy Fan
Date:


On Sun, Feb 14, 2021 at 7:56 PM Michael Paquier <michael@paquier.xyz> wrote:
On Sun, Feb 14, 2021 at 09:29:08AM +0800, Andy Fan wrote:
> Thank you tom for the reply.  What would be the difference between the
> SPI and "write a pure SQL UDF" and call it with DirectFunctionCall1? I
> just ran into a similar situation some days before.   Currently I think
> DirectFunctionCall1 doesn't need to maintain a connection but SPI has to
> do that.

Hard to say without knowing your use case.  A PL function is more
simple to maintain than a C function, though usually less performant
from the pure point of view of its operations.  A SQL function could
finish by being inlined, allowing the planner to apply optimizations
as it would know the function body.  Going with SPI has the advantage
to have code able to work without any changes across major versions,
which is a no-brainer when it comes to long-term maintenance.
--
Michael

Thank you Michael for the response.

--
Best Regards