[SQL] Accessing elements of bytea[] always returns NULL

2008-10-01 Thread Sestak, John S 
Hi all,
I'm trying to pass in a bytea array (i.e. bytea[]) into my stored
procedure but when I try to access the elements in the array using an
array index it always returns NULL. In other words, if I do the
following:

CREATE OR REPLACE FUNCTION some_function( _test BYTEA[] )
RETURNS BIGINT AS
$BODY$
DECLARE
some_var BYTEA;
BEGIN

some_var := _test[1];

RETURN 0;

END;

...

The variable 'some_var' is always NULL. Technically '_test' is a
one-dimensional array so I would think this would work but it doesn't.
So, how do you access the bytea elements of a bytea array? I'm pretty
sure the elements of the array have data in them because when I call
array_dims() it returns the correct dimensions for the array that has
been passed in.



 

Could it be the JDBC code?  Here's how I'm passing the bytea array data
in:

 

public static void addHashes(Connection connection) throws SQLException
{
CallableStatement cs = null;

try {
cs = connection.prepareCall("{ ? = call some_function(?) }");

cs.registerOutParameter(1, Types.BIGINT);

// Initialize with some data
byte[][] hashes = new byte[2][2];
hashes[0][0] = 1;
hashes[0][1] = 2;
hashes[1][0] = 3;
hashes[1][1] = 4;

java.sql.Array byte_array = connection.createArrayOf("bytea", hashes);
cs.setArray(2, byte_array);

cs.execute();
}
catch( Exception e)
{
log.error("Error: Caught exception - " + e.getMessage());
}
finally
{
if (cs != null)
{
cs.close()
}
}
}


Thank you,
John

Re: [SQL] Accessing elements of bytea[] always returns NULL

2008-10-01 Thread Tom Lane 
"Sestak, John S" <[EMAIL PROTECTED]> writes:
> I'm trying to pass in a bytea array (i.e. bytea[]) into my stored
> procedure but when I try to access the elements in the array using an
> array index it always returns NULL.

Works fine from SQL, so I suppose there's something wrong with your
JDBC code.  You'd likely have better luck asking on pgsql-jdbc.

regards, tom lane

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Can COPY update or skip existing records?

2008-10-01 Thread Tony Wasson 
On Tue, Sep 30, 2008 at 5:16 AM, Glenn Gillen <[EMAIL PROTECTED]> wrote:
> Hey all,
>
> I've got a table with a unique constraint across a few fields which I
> need to regularly import a batch of data into. Is there a way to do it
> with COPY without getting conflicts on the unique contraint? I have no
> was of being certain that some of the data I'm trying to load isn't in
> the table already.
>
> Ideally I'd like it to operate like MySQL's on_duplicate_key_update
> option, but for now I'll suffice with just ignoring existing rows and
> proceeding with everything else.

I ran into a similar problem. I'm using these merge_by_key functions:

http://pgfoundry.org/projects/mbk

Here's a quick example...

CREATE TEMP TABLE foo (LIKE dst INCLUDING DEFAULTS);

COPY foo (c1, c2) FROM STDIN;
(your copy data here)
\.

SELECT * FROM merge_by_key(
'public', -- table schema
'dst', -- table name
'mnew.c2 < mold.c2', -- merge condition
'select c1,c2 FROM foo'
);

Disclaimer: The author is a friend of mine. :-)

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql