[SQL] Text cast problem
Dear all- I encountered the following problem: select * from parcel where number = '255 ' gives me 1 row but select * from parcel where number = '255 ' ::text does give me 0 rows. The cast removes the trailing empty character. Why is that? Unfortunately the statements are generated (NPGSQL) and I can’t change the way they are generated. What can I do? Any suggestions highly welcomed! Volkmar
Re: [SQL] FUNCTION problem
On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better explanation- http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver akla...@comcast.net For this particular case the following works. CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record AS $Body$ DECLARE croid integer; DECLARE R RECORD; BEGIN SELECT INTO croid 2; SELECT INTO R croid,$1; RETURN R; END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better explanation- http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver akla...@comcast.net For this particular case the following works. CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record AS $Body$ DECLARE croid integer; DECLARE R RECORD; BEGIN SELECT INTO croid 2; SELECT INTO R croid,$1; RETURN R; END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver akla...@comcast.net Forgot to show how to call it. test=# SELECT * from test_function(1) as test(c1 int,c2 int); c1 | c2 + 2 | 1 (1 row) -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
Adrian Klaver wrote: On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better explanation- http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver akla...@comcast.net For this particular case the following works. CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record AS $Body$ DECLARE croid integer; DECLARE R RECORD; BEGIN SELECT INTO croid 2; SELECT INTO R croid,$1; RETURN R; END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver akla...@comcast.net Forgot to show how to call it. test=# SELECT * from test_function(1) as test(c1 int,c2 int); c1 | c2 + 2 | 1 (1 row) Ah!, I see what you mean about the definition of 'RECORD'. (The lights come on...) And here I thought it would all be so simple. You show a valid, and most informative solution. This should get things working for me. Thank you very much for your help. Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
Adrian Klaver wrote: If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument list to eliminate the as test(c1 int,c2 int) clause. At this point it becomes a A--B--C problem i.e determine what your inputs are, how you want to process them and how you want to return the output. '8.1+'?? Hmmm, I'm using 8.3. I could use that. I got the more complex version of the query to work by backing away from 'plpgsql' as the language and using 'sql' instead. I then nested (terribly ugly) my select statements to generate a single SQL query from all. This allows me to change the output of the query without needing to define a new set of output 'OUT' parameters each time I change things. I have use of the 'OUT' parameters with another set of functions though. Thanks for that. Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pl/pgsql or control structures outside of a function?
Hi all,
Is there any way to use PL/pgSQL code outside of a function?
The reason I'm asking is that I'm porting some code from
sybase/isql/SQR, and it allows some control code structures to be used
in an sql script. For instance,
begin
if ((select count(*) from users where login = 'foo') = 0)
begin
print 'Login foo does not exist.'
end
else
begin
print 'Adding account for foo.'
insert into accounts values ('foo', 'bar')
end
end
PL/pgSQL looks like it would make this port rather easy, but all the
docs and examples I found never had an example of PL/pgSQL outside of
a function. For the purposes of this port I'd really prefer not to
create functions for all this. I searched through the PL/pgSQL docs
and even several Google searches but couldn't find a definitive
answer. It's fine if the answer is no, I'm just curious if I should
pursue this path or look for a different one. And if there's a way to
do this or something like it besides create scripts in
Perl/Python/etc. that you know of, I'd appreciate any information.
Thanks,
Peter
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pl/pgsql or control structures outside of a function?
On Apr 3, 2009, at 5:03 PM, Peter Koczan wrote: Is there any way to use PL/pgSQL code outside of a function? No. The reason I'm asking is that I'm porting some code from sybase/isql/SQR, and it allows some control code structures to be used in an sql script. For instance, CASE might work for you. http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html John DeSoi, Ph.D. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
