Re: [SQL] Multiple return values and assignment
On 2009-04-25, Leif B. Kristensen 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 order > number from a text, and conditionally modify the text: > > CREATE OR REPLACE FUNCTION get_sort(INTEGER, INTEGER, TEXT) > RETURNS int_text AS $$ ... > To use the two values in an other function where I've declared a > variable sort_text of type int_text, I do like this: > > SELECT number, string FROM get_sort(par_id, srt, txt) INTO sort_text; the above is equivalent to sort_text = get_sort(par_id, srt, txt); > srt := sort_text.number; > txt := sort_text.string; > But I feel it's a little awkward. Is there a more elegant way to do it? SELECT * FROM get_sort(par_id, srt, txt) INTO srt,txt; -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] varchar value comparisons not working?
On 2009-04-24, Shawn Tayler wrote: > Hello, > > I'm doing a quick comparison between a couple tables, trying to cleanup > some inconsistencies, and what should be a simple check between 2 tables > doesn't seem to be working. psql is 8.3.7 and server is 8.2.13. > > I run the following: > > select sfd.lid as sflid,sd.lid as slid,sfd.serial from sfd,shawns_data > sd where sfd.serial = sd.serial_number order by sfd.lid; > > the lid columns in both tables should be identical, but as you see in > this sample, they do differ: > > sflid | slid | serial > ---+---+-- > 14056 | 14056 | 9614583 > 14057 | | 9614984 > 14058 | 14058 | 9614737 > 14059 | 14059 | 9614579 > 14060 | | 9614827 > 14061 | 14061 | 9614726 > 14062 | 14062 | 9614966 > 14063 | 14063 | 9615079 > > So running this query: > > select count(*) from sfd,shawns_data sd where sfd.serial = sd.serial_number > and sfd.lid != sd.lid; > > I should show some rows that do not match, at least 2 (there are more than > shown). > > But instead I get this: > > count > --- > 0 > (1 row) > > > What am I doing wrong? expecting NULL values not-equal something. select count(*) from sfd,shawns_data sd where sfd.serial = sd.serial_number and COALESCE( sfd.lid != sd.lid, TRUE ) the above include rows where both are NULL, if that's undesirable they must be explicitly excluded. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query with Parameters and Wildcards
landsharkdaddy wrote: I have not tried that but I will in the morning. The @ in SQL is used to indicate a parameter passed to the query. In PostgreSQL it seems that the : is the same as the @ in SQL Server. I tried something like: SELECT * FROM Customers WHERE FirstName LIKE :custfirst + '%'; And it told me that the + could not be used. Not sure the exact message but I will check again tomorrow and see what it was and post the results. T-SQL defines that variables need to start with @ (like, for instance, in PHP they star with $). In postgres you have positional parametars, $1, for instance. You could, for instance, write SQL function in postgres that would do what you need: CREATE FUNCTION get_customers_with_like (a_name_part character varying) RETURNS SETOF customers AS $$ SELECT * FROM customers WHERE firstname LIKE $1 || '%'; $$ LANGUAGE 'sql'; In postgres, you use '||' for string concatenation (instead of '+' in T-SQL). Mario -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query with Parameters and Wildcards
When I try the following like you suggested I get an error that says "operator does not exist: || unknown SELECT customercellphone, customercity, customerdatecreated, customerdatelastmodified, customeremail, customerfax, customerfirstname, customerid, customerlastname, customermiddleinitial, customerphone, customerreferredby, customerstateabbr, customerstreet1, customerstreet2, customersuffix, customertitle, customerworkphone, customerworkphoneext, customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE || '%') landsharkdaddy wrote: > > I have a query that works on SQL Server to return customers that contain > the string entered by the user by accepting parameters and using the LIKE > keyword. I would like to move this to postgreSQL but I'm just not sure how > to get it done. This is the query > > SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%'; > > This works great on SQL Server but not on postgreSQL. Any help would be > appreciated. > -- View this message in context: http://www.nabble.com/Query-with-Parameters-and-Wildcards-tp23248274p23257346.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query with Parameters and Wildcards
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, customerfax, customerfirstname, customerid, customerlastname, customermiddleinitial, customerphone, customerreferredby, customerstateabbr, customerstreet1, customerstreet2, customersuffix, customertitle, customerworkphone, customerworkphoneext, customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE $1 || '%') $$ LANGUAGE SQL; Please tell me is it work for you. Regards Hidayat - Original Message - From: "landsharkdaddy" To: Sent: Monday, April 27, 2009 9:19 PM Subject: Re: [SQL] Query with Parameters and Wildcards > > When I try the following like you suggested I get an error that says > "operator does not exist: || unknown > > > > SELECT customercellphone, customercity, customerdatecreated, > customerdatelastmodified, customeremail, customerfax, customerfirstname, > customerid, customerlastname, customermiddleinitial, customerphone, > customerreferredby, customerstateabbr, customerstreet1, customerstreet2, > customersuffix, customertitle, customerworkphone, customerworkphoneext, > customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE || > '%') > > > > > > landsharkdaddy wrote: >> >> I have a query that works on SQL Server to return customers that contain >> the string entered by the user by accepting parameters and using the LIKE >> keyword. I would like to move this to postgreSQL but I'm just not sure >> how >> to get it done. This is the query >> >> SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%'; >> >> This works great on SQL Server but not on postgreSQL. Any help would be >> appreciated. __ Apakah Anda Yahoo!? Lelah menerima spam? Surat Yahoo! memiliki perlindungan terbaik terhadap spam http://id.mail.yahoo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query with Parameters and Wildcards
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. The function from dayat has a small mistake. It should be: CREATE OR REPLACE FUNCTION TEST(lastname VARCHAR) else it will result in an error because of the missing space. On Monday 27 of April 2009 16:47:40 dayat wrote: > 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, customerfax, customerfirstname, > customerid, customerlastname, customermiddleinitial, customerphone, > customerreferredby, customerstateabbr, customerstreet1, customerstreet2, > customersuffix, customertitle, customerworkphone, customerworkphoneext, > customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE $1 || > '%') > $$ > LANGUAGE SQL; > > Please tell me is it work for you. > > Regards > Hidayat > > - Original Message - > From: "landsharkdaddy" > To: > Sent: Monday, April 27, 2009 9:19 PM > Subject: Re: [SQL] Query with Parameters and Wildcards > > > When I try the following like you suggested I get an error that says > > "operator does not exist: || unknown > > > > > > > > SELECT customercellphone, customercity, customerdatecreated, > > customerdatelastmodified, customeremail, customerfax, customerfirstname, > > customerid, customerlastname, customermiddleinitial, customerphone, > > customerreferredby, customerstateabbr, customerstreet1, customerstreet2, > > customersuffix, customertitle, customerworkphone, customerworkphoneext, > > customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE || > > '%') > > > > landsharkdaddy wrote: > >> I have a query that works on SQL Server to return customers that contain > >> the string entered by the user by accepting parameters and using the > >> LIKE keyword. I would like to move this to postgreSQL but I'm just not > >> sure how > >> to get it done. This is the query > >> > >> SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%'; > >> > >> This works great on SQL Server but not on postgreSQL. Any help would be > >> appreciated. > > __ > Apakah Anda Yahoo!? > Lelah menerima spam? Surat Yahoo! memiliki perlindungan terbaik terhadap > spam http://id.mail.yahoo.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query with Parameters and Wildcards
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 would like to know why SELECT * FROM lanemanger.customers WHERE (customerfirstname ILIKE $1 || '%') doesnt work when used as the select query of the dataset. Using functions is just as well but I would like to understand why the query doesnt work unless it is called from a function. Thanks to all that helped with this one, you have been very helpful. Jure Kobal wrote: > > 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. > > The function from dayat has a small mistake. It should be: > CREATE OR REPLACE FUNCTION TEST(lastname VARCHAR) > else it will result in an error because of the missing space. > > On Monday 27 of April 2009 16:47:40 dayat wrote: >> 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, customerfax, customerfirstname, >> customerid, customerlastname, customermiddleinitial, customerphone, >> customerreferredby, customerstateabbr, customerstreet1, customerstreet2, >> customersuffix, customertitle, customerworkphone, customerworkphoneext, >> customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE $1 >> || >> '%') >> $$ >> LANGUAGE SQL; >> >> Please tell me is it work for you. >> >> Regards >> Hidayat >> >> - Original Message - >> From: "landsharkdaddy" >> To: >> Sent: Monday, April 27, 2009 9:19 PM >> Subject: Re: [SQL] Query with Parameters and Wildcards >> >> > When I try the following like you suggested I get an error that says >> > "operator does not exist: || unknown >> > >> > >> > >> > SELECT customercellphone, customercity, customerdatecreated, >> > customerdatelastmodified, customeremail, customerfax, >> customerfirstname, >> > customerid, customerlastname, customermiddleinitial, customerphone, >> > customerreferredby, customerstateabbr, customerstreet1, >> customerstreet2, >> > customersuffix, customertitle, customerworkphone, customerworkphoneext, >> > customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE || >> > '%') >> > >> > landsharkdaddy wrote: >> >> I have a query that works on SQL Server to return customers that >> contain >> >> the string entered by the user by accepting parameters and using the >> >> LIKE keyword. I would like to move this to postgreSQL but I'm just not >> >> sure how >> >> to get it done. This is the query >> >> >> >> SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%'; >> >> >> >> This works great on SQL Server but not on postgreSQL. Any help would >> be >> >> appreciated. >> >> __ >> Apakah Anda Yahoo!? >> Lelah menerima spam? Surat Yahoo! memiliki perlindungan terbaik terhadap >> spam http://id.mail.yahoo.com > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > -- View this message in context: http://www.nabble.com/Query-with-Parameters-and-Wildcards-tp23248274p23260790.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query with Parameters and Wildcards
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 varchar, par2 varchar) then $1 represents the first parameter and $2 the second one. You can't use position parameters outside of functions. For your needs a normal query would do it. The query that's send to the server would need to look something like this: SELECT * FROM lanemanger.customers WHERE (customerfirstname ILIKE 'lastname' || '%') lastname would be the one you're searching for. Now I have a question about your original query: SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%'; As much as I know @ in mssql is used only inside of procedures and since you're using C# isn't @custfirst in the end a C# variable and has nothing to do with the sql server? If I'm right with that then wouldn't the query for postgres look like: SELECT * FROM Customers WHERE FirstName ILIKE (@custfirst || '%'); Could be you will have to enclose the @custfirst in single quotas to make it work but how to do it right depends on the programming language you use. Regards, Jure On Monday 27 of April 2009 19:11:09 landsharkdaddy wrote: > 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 would like to know why > > SELECT * FROM lanemanger.customers WHERE (customerfirstname ILIKE $1 || > '%') > > doesnt work when used as the select query of the dataset. Using functions > is just as well but I would like to understand why the query doesnt work > unless it is called from a function. Thanks to all that helped with this > one, you have been very helpful. > > Jure Kobal wrote: > > 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. > > > > The function from dayat has a small mistake. It should be: > > CREATE OR REPLACE FUNCTION TEST(lastname VARCHAR) > > else it will result in an error because of the missing space. > > > > On Monday 27 of April 2009 16:47:40 dayat wrote: > >> 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, customerfax, customerfirstname, > >> customerid, customerlastname, customermiddleinitial, customerphone, > >> customerreferredby, customerstateabbr, customerstreet1, customerstreet2, > >> customersuffix, customertitle, customerworkphone, customerworkphoneext, > >> customerzip FROM lanemanager.customers WHERE (customerlastname ILIKE $1 > >> > >> '%') > >> $$ > >> LANGUAGE SQL; > >> > >> Please tell me is it work for you. > >> > >> Regards > >> Hidayat > >> > >> - Original Message - > >> From: "landsharkdaddy" > >> To: > >> Sent: Monday, April 27, 2009 9:19 PM > >> Subject: Re: [SQL] Query with Parameters and Wildcards > >> > >> > When I try the following like you suggested I get an error that says > >> > "operator does not exist: || unknown > >> > > >> > > >> > > >> > SELECT customercellphone, customercity, customerdatecreated, > >> > customerdatelastmodified, customeremail, customerfax, > >> > >> customerfirstname, > >> > >> > customerid, customerlastname, customermiddleinitial, customerphone, > >> > customerreferredby, customerstateabbr, customerstreet1, > >> > >> customerstreet2, > >> > >> > customersuffix, customertitle, customerworkphone, > >> > customerworkphoneext, customerzip FROM lanemanager.customers WHERE > >> > (customerlastname ILIKE || '%') > >> > > >> > landsharkdaddy wrote: > >> >> I have a query that works on SQL Server to return customers that > >> > >> contain > >> > >> >> the string entered by the user by accepting parameters and using the > >> >> LIKE keyword. I would like to move this to postgreSQL but I'm just > >> >> not sure how > >> >> to get it done. This is the query > >> >> > >> >> SELECT * FROM Customers WHERE FirstName LIKE @custfirst + '%'; > >> >> > >> >> This works great on SQL Server but not on postgreSQL. Any help would > >> > >> be > >> > >> >> appreciated. > >> > >> __ > >> Apakah Anda Yahoo!? > >> Lelah menerima spam? Surat Yahoo! memiliki perlindungan terbaik > >> terhadap spam http://id.mail.yahoo.com > > > > -- > > Sen
[SQL] Storing null bytes in bytea
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. The session data (objects/class instances
etc) is serialized into raw bytes in PHP and contains a few nul/zero
bytes (which are meaningful to PHP when it comes to deserializing the data.)
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 database and the remaining data is omitted.
An example of such query is this:
INSERT INTO system.session (user_id, session_key, session_name,
client_browser, date_created, date_expires, ip_address, session_data)
VALUES (NULL, '4pc4sjciahoc4fuk1bt4kohe91'::character varying(32),
'AppName'::character varying(50), 'Mozilla/5.0 (Windows; U; Windows NT
6.0; en-GB; rv:1.9.0.9) Gecko/2009040821 Firefox/3.0.9 (.NET CLR
3.5.30729)'::character varying(200),
public.get_pg_timestamp(1240853862::integer),
public.get_pg_timestamp(1240854162::integer), '192.168.0.8'::inet,
E'IsLoggedIn|b:1;CurrentUser|O:17:"Class_SystemUser":4:{s:26:"\\000Class_SystemUser}'::bytea);
All other columns are fine, but when it comes to the session_data
column, all I end up with is
'IsLoggedIn|b:1;CurrentUser|O:17:"Class_SystemUser":4:{s:26:"'.
Everything past the first "\\" byte sequence is ignored.
I've tried this with and without the 'E' at the beginning of the value
string.
Any pointers as to what I'm doing wrong?
Thanks,
Andy
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Multiple return values and assignment
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, txt; as that seems a little tidier. I probably should have thought of it myself, but I wrongly assumed that it was impossible to do multiple assignments like this in plpgsql. -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Storing null bytes in bytea
Andy Shellam 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 database and the remaining data is omitted.
Your example works fine in psql:
regression=# create table t1 (f1 bytea);
CREATE TABLE
regression=# insert into t1 values
(E'IsLoggedIn|b:1;CurrentUser|O:17:"Class_SystemUser":4:{s:26:"\\000Class_SystemUser}'::bytea);
INSERT 0 1
regression=# select * from t1;
f1
---
IsLoggedIn|b:1;CurrentUser|O:17:"Class_SystemUser":4:{s:26:"\000Class_SystemUser}
(1 row)
I suspect what is happening is that some layer on the client side is
doubling (or perhaps undoubling?) the backslashes for you. Exactly
what are you doing with that literal as you build the query? It might
help to turn on log_statements so that you can see just what the
server is getting.
regards, tom lane
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
