[SQL] Please help, can't figure out what's wrong with this function...
Hello group, I 've written the following function: CREATE OR REPLACE FUNCTION public.getstadtlandflussentrybyid (integer) RETURNS SETOF public.ty_stadtlandflussentry AS'DECLARE objReturn ty_stadtlandflussentry; DECLARE iid integer;BEGIN iid := $1; for objReturn IN SELECT ste_id, ste_type, ste_name, ste_firstwrongname, ste_secondwrongname, ste_description, ste_online FROM tbl_stadtlandflussentry WHERE ste_id=iid loop RETURN next objReturn; END LOOP; RETURN;END;'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; When I try to execute the function by calling SELECT getstadtlandflussentrybyid(1); I get the following error: ERROR: missing .. at end of SQL _expression_ I haven't figured out what this message wants to tell me and why it is thrownat all. Maybe someone can give me a hint, Thanks in advance, Moritz PS: the function should run under postgres 7.4and created the following type: CREATE TYPE public.ty_stadtlandflussentry AS ( ste_id BIGINT, ste_type INTEGER, ste_name VARCHAR(100), ste_firstwrongname VARCHAR(100), ste_secondwrongname VARCHAR(100), ste_description TEXT, ste_online INTEGER);
Re: [SQL] Please help, can't figure out what's wrong with this function...
On Sep 12, 2005, at 8:14 AM, Moritz Bayer wrote: I get the following error: ERROR: missing .. at end of SQL expression it looks like your for loop is being interpreted as the integer variant, e.g. for i in 1..10 loop CREATE TYPE public.ty_stadtlandflussentry AS ( DECLARE objReturn ty_stadtlandflussentry; Maybe it needs to be: declare objReturn public.ty_stadtlandflussentry%rowtype; John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Please help, can't figure out what's wrong with this function...
Moritz Bayer [EMAIL PROTECTED] writes: I get the following error: ERROR: missing .. at end of SQL expression I haven't figured out what this message wants to tell me and why it is thrown at all. I think it's telling you that you are using a 7.3 or older server. Try 7.4 or later --- plpgsql was pretty weak on handling rowtype variables that far back. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] refer a column as a varible name?
Hi all. I have troubles trying to achieve this assignment: suppose the type mycolumn as (field1, varchar, field2 varchar) and field_name = ''field1'' and returnValue declared as mycolumn ... can i say returnValue.$field_name = ''ok''? There is a way to achieve this piece of code? Thanks a lot! -- Gerardo Herzig Direccion General de Organizacion y Sistemas Facultad de Medicina U.B.A. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] refer a column as a varible name?
On Mon, Sep 12, 2005 at 12:21:22PM -0300, [EMAIL PROTECTED] wrote:
suppose the
type mycolumn as (field1, varchar, field2 varchar)
and
field_name = ''field1''
and returnValue declared as mycolumn
...
can i say returnValue.$field_name = ''ok''?
To achieve this in PL/pgSQL you'll need to use a conditional statement
(IF field_name = 'field1' THEN ...). I'm not sure if a solution
involving EXECUTE is possible; if so then it's probably non-obvious.
What version of PostgreSQL are you using, and do you have a requirement
to use PL/pgSQL? In 8.0 PL/Perl can return composite types and such
an assignment would be trivial:
CREATE TYPE mycolumn AS (field1 varchar, field2 varchar);
CREATE FUNCTION foo(varchar) RETURNS mycolumn AS $$
my $field_name = $_[0];
my $returnValue = {$field_name = ok};
return $returnValue;
$$ LANGUAGE plperl IMMUTABLE STRICT;
SELECT * FROM foo('field1');
field1 | field2
+
ok |
(1 row)
SELECT * FROM foo('field2');
field1 | field2
+
| ok
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Need help with `unique parents` constraint
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you for an excellent answer. I think I will have to study your code for a while. But is it such a bad idea to have a separate column for the primary key here? I see that there are two schools on this, with diametrically opposed views. For my own part, I feel that it at least doesn't hurt to have a surrogate key. Secondly, a single key value is easier to reference from another table than a composite key. Not bad, but perhaps slightly inefficient and redundant. It depends on how your table is actually structured, but if the only way your app will ever refer to that table is in the context of those 2 foreign keys, then it makes sense to go ahead and make them a primary key. If there are other important fields in the table, /and/ if it is referenced from other tables, then I might add another column. But generally, this should be the exception and not the rule. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200509122031 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFDJh99vJuQZxSWSsgRAiRFAJwKiGVsJhcbxIe0nQ3bnxJUZupucACgnUa/ 57e9UDfVkv/4AMp2wpqEa3c= =20d1 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] user defined type, plpgsql function and NULL
Hi, let's say one has an user defined data type CREATE TYPE foobar_t AS ( va varchar(25), vb varchar(4), vc varchar(20), ia integer, ib integer ); and a stored procedure in plgpsql (stripped and sample only): CREATE OR REPLACE FUNCTION foobar(int, foobar_t, int, varchar) RETURNS INT AS' DECLARE ia ALIAS FOR $1; fbt ALIAS FOR $2; ib ALIAS FOR $3; vc ALIAS FOR $4; BEGIN ... IF fbt IS NULL THEN RAISE NOTICE ''fbt IS NULL;''; ELSE RAISE NOTICE ''fbt IS NOT NULL... ''; IF fbt.va IS NULL THEN RAISE NOTICE ''fbt.va IS NULL;''; ELSE RAISE NOTICE ''fbt.va = %'', fbt.va; END IF; ... END IF; ... RETURN 0; END' LANGUAGE plpgsql; If one does a SELECT foobar(1, NULL, 2, 'end'); NOTICE: ia = 1 NOTICE: fbt IS NOT NULL... NOTICE: fbt.va IS NULL; NOTICE: fbt.vb IS NULL; NOTICE: fbt.vc IS NULL; NOTICE: fbt.ia IS NULL; NOTICE: fbt.ib IS NULL; NOTICE: ib = 2 NOTICE: vc = end foobar 0 (1 row) Note the second argument foobar_t is given as NULL but $2 IS NOT NULL. I cannot find anything about this in the docs but I asume that the single NULL will implicitly set all attributes to NULL? Is this correct or is it just a works like that this time but may change at any time in the future? -- Greetings Bjoern A. Zeeb ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Performance issue
On Tue, Aug 30, 2005 at 03:38:52PM +0700, Ricky Sutanto wrote: I use Apache Web Server and PostgreSQL 7.3 to collect data everyday. Now it has been 5 month since I install that server. I wonder why now my web very slow to retrieve and display data? When I check the memory, I found that postgreSQL client seem not release after allocate. I try to find bug on my script, but everything look clean to me. Anyone have experience like me.. please share info with me You haven't really provided much information on your problem. Are you vacuuming regularly? Tim Thanks anyway, Ricky Sutanto ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Tim Goodaire416-673-4126[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. signature.asc Description: Digital signature
Re: [SQL] Indexing an array?
Silke Trissl wrote: As far I could read from the documentation - this should be possible. But my question is, is there a kind of index on the array. If your needs are a bit more modest (say, a few thousands instead of billions) the stuff in contrib/intarray works well; and if you needed types other than integers you can you can look at intarray to see how to build indexes on them and what those indexes are useful for. Lets say, I want to get element 2,675,345,328. Does Postgres have to load the entire array into memory and then run through the 2.6 billion characters to return the one I want or does Postgres have an index - as where to find this element on disk? Wouldn't you rather expect an index to be useful for finding which rows match your query rather than for finding content within a row? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Age in days
If you need to know the age to the nearest day on the present day
I think you can use something like:
select date_trunc('day',age(now(),dateofbirth)) from people
gives:
3 years 2 mons 12 days
My questions is, is there any way to convert that figure into an age
expressed as a number days only?
Many thanks
Mark
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Age in days
This will give you the no of days.select current_date - dateofbirth::date from people;
On 9/9/05, Mark A. Strivens [EMAIL PROTECTED] wrote:
If you need to know the age to the nearest day on the present dayI think you can use something like:select date_trunc('day',age(now(),dateofbirth)) from peoplegives:3 years 2 mons 12 days
My questions is, is there any way to convert that figure into an ageexpressed as a number days only?Many thanksMark---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings-- with regards,S.GnanavelSatyam Computer Services Ltd.
Re: [SQL] Age in days
On Sep 10, 2005, at 1:04 AM, Mark A. Strivens wrote:
select date_trunc('day',age(now(),dateofbirth)) from people
My questions is, is there any way to convert that figure into an age
expressed as a number days only?
Try
select current_date - dateofbirth from people;
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] How do I convert an integet to a timestamp?
On Mon, Sep 12, 2005 at 11:01:08AM -0700, Wei wrote: I followed the doc and tried select CAST(1126547334 AS timestamp) and I only got an error response that says: ERROR: cannot cast type integer to timestamp without time zone. What documentation suggested casting an integer to a timestamp? What is the proper way to do the conversion? See Date/Time Functions and Operators -- it has an example of what you're trying to do: http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
