Re: [SQL] Multiple return values and assignment

2009-04-27 Thread Jasen Betts
On 2009-04-25, Leif B. Kristensen l...@solumslekt.org wrote: I've got a function that returns both an integer and a string as a user-defined composite type int_text: -- CREATE TYPE int_text AS (number INTEGER, string TEXT); Basically, the function does some heuristics to extract a sort

Re: [SQL] Query with Parameters and Wildcards

2009-04-27 Thread dayat
Do you run this code in the function? If so, the following example function with LANGUAGE SQL function: CREATE OR REPLACE FUNCTION TEST(lastnameVARCHAR) RETURNS SETOF lanemanager.customers AS $$ SELECT customercellphone, customercity, customerdatecreated, customerdatelastmodified, customeremail,

Re: [SQL] Query with Parameters and Wildcards

2009-04-27 Thread Jure Kobal
In the original select you missed a small part. The operation || needs a value on every side and you missed the value on the left side. You had it as: WHERE (customerlastname ILIKE || '%') instead of WHERE (customerlastname ILIKE 'lastname' || '%'). And that is the reason for the error you got.

Re: [SQL] Query with Parameters and Wildcards

2009-04-27 Thread landsharkdaddy
That is it! It works just fine and from my Typed Dataset in c# I just call the function using SELECT * FROM lanemanager.GetCustomerByLastName(:customerlastname) and it works perfectly. Through this whole process I have also gained a greater understanding of using Functions as well. I guess I

Re: [SQL] Query with Parameters and Wildcards

2009-04-27 Thread Jure Kobal
Hope I got your question right and I will somehow manage to explain it in a simple way. SELECT * FROM lanemanger.customers WHERE (customerfirstname ILIKE $1 || '%') Here you use $1 which is the position parameter in the function. So if you create the function as CREATE FUNCTION test(par1

[SQL] Storing null bytes in bytea

2009-04-27 Thread Andy Shellam
Hi all, I was going to post this on the pgsql-php list but I think the issue is more on the PostgreSQL side of things. I'm using PHP 5.2.9 connected to a PostgreSQL 8.3.7 server running on Solaris 10 to try to store the session data for an application using a custom session handler class.

Re: [SQL] Multiple return values and assignment

2009-04-27 Thread Leif B. Kristensen
On Monday 27. April 2009, Jasen Betts wrote: SELECT * FROM get_sort(par_id, srt, txt) INTO srt,txt; Thank you very much! That saved me from one composite variable declaration and two superfluous lines of code. I've settled for SELECT number, string FROM get_sort(par_id, srt, txt) INTO srt,

Re: [SQL] Storing null bytes in bytea

2009-04-27 Thread Tom Lane
Andy Shellam andy-li...@networkmail.eu writes: Because of the nul bytes, I've set the session_data column to be a bytea column in my database table. However I cannot get PostgreSQL to read past the first nul byte on an insert, so the unserialize call fails when it reads it back out the