Skip site navigation (1) Skip section navigation (2)

Inserting data a UDT in binary format using LIBPQ

From: "Brijesh Shrivastav" <Bshrivastav(at)esri(dot)com>
To: <pgsql-interfaces(at)postgresql(dot)org>
Cc: "Brijesh Shrivastav" <Bshrivastav(at)esri(dot)com>
Subject: Inserting data a UDT in binary format using LIBPQ
Date: 2007-01-09 17:30:22
Message-ID: (view raw or whole thread)
Lists: pgsql-interfaces
Dear PostgreSQL Gurus,

I need your help. I have created a UDT that has both text
and binary functions (func_IN and func_recv) to recieve
text and binary data. I have also added some other functions
for new type that takes binary and text input. Everything 
seems to be working fine if I am using psql. I can load and query
data. However, I cannot seem to insert data using LibPQ. It calls
func_recv functions on the server side but doesn't get the
data it expects. I have created the type be of variable length
and am 'detoasting' the data inside recv function to make sure
data input is properly parsed. 

Included below is a snippet of code cut and places from various 
functions that I am using to insert. On server side when I try to print 
input data size and values I get incorrect results.

I didn't find much documentation but thought this should have 
worked. Please point any thing that I didn't do right. Also, another
function that takes binary input in the very same manner and can be
invoked at sql level seems to work just fine.


Client side code:
  sprintf(m_psql," Insert into %s (UDTCOL) VALUES "
    " ($1)", TEST_TABLE);

  m_paramTypes[0] = 50415; //OID for the new type from pg_type table
  m_paramValues[0] = "A text string to test the data input"; 
  m_paramSizes[0] = strlen(m_paramValues[0]);
  m_paramFormats[0] = PG_BINARY_FORMAT;
  m_res = PQprepare(m_pgconn, m_stmtName,m_psql, 1,m_paramTypes);
  if (PQresultStatus(m_res) != PGRES_COMMAND_OK)

 m_res = PQexecPrepared(m_pgconn,
                         (const int  *) m_paramSizes,
                         (const int *) m_paramFormats,

  if (PQresultStatus(m_res) != PGRES_COMMAND_OK &&
      PQresultStatus(m_res) != PGRES_TUPLES_OK)


Server side code:

Datum func_Recv(PG_FUNCTION_ARGS)

    elog(NOTICE," Binary DATA_LEN = %d\n",VARSIZE(input));
    elog(NOTICE," Binary DATA CONTENT= %s\n",VARDATA(input));


pgsql-interfaces by date

Next:From: Tom LaneDate: 2007-01-09 21:17:20
Subject: Re: Inserting data a UDT in binary format using LIBPQ
Previous:From: Bruce MomjianDate: 2007-01-09 03:48:02
Subject: Re: BCC55 and libpq 8.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group