Re: [SQL] cursors and for loops?
On Sunday 11 April 2004 19:46, Dennis wrote: > Tom Lane writes: > > Something like > > > > LOOP > > FETCH ...; > > EXIT WHEN NOT found; > > ... > > END LOOP; > > Thank you! I tried finding documentation on "found" in this context and > didn't come up with anything. Can you point me to where it is documented? In my 7.3.x docs, at the end "Basic statements" section of the "plpgsql" chapter. There is only one sentence on it. > Also, I am not getting the results I think I should be getting. Is there > any kind of debug setting, or if not that, a way to output text (i.e. > printf) from plpgsql? RAISE NOTICE ''var1 = %, var2 = %'', var1, var2; Note - you need this format, you can't mix and match expressions etc. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] trigger/for key help
Greg Stark <[EMAIL PROTECTED]> writes: > Is there a reason postgres goes out of its way to pick names that > will be harder to work with than necessary? If we use ordinary identifiers for system-generated names then we will be infringing on user name space --- ie, there's a potential for conflict. I suppose we could use long randomly-generated names like ewjncm343cnlen, but are those really easier to work with? I think a more useful approach is to treat it as a documentation problem. Perhaps an example in the ALTER TABLE man page would help. regards, tom lane ---(end of broadcast)--- TIP 3: 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
[SQL] Formatting Functions and Group By
Hi, I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem with one particular area. For example, a query like this works in MySQL: select to_char(myCol,'Mon YY') from myTable group by to_char(myCol,'MM ') order by to_char(myCol,'MM ') Postgres will give me an error saying that "to_char(myCol,'Mon YY')" must be in the 'group by'. I understand why that normally needs to be the case, but in theory, it would be ok here because they are actually the same values (in the select and group by) but just formatted differently. I just want the query to be grouped and ordered by month and year, but to be formatted differently in the output. Any ideas? Thanks!! __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Formatting Functions and Group By
On Tue, Apr 13, 2004 at 09:13:05 -0700,
Terry Brick <[EMAIL PROTECTED]> wrote:
> Hi,
> I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem
> with one
> particular area. For example, a query like this works in MySQL:
>
> select
> to_char(myCol,'Mon YY')
> from
> myTable
> group by
> to_char(myCol,'MM ')
> order by
> to_char(myCol,'MM ')
>
> Postgres will give me an error saying that "to_char(myCol,'Mon YY')" must be in the
> 'group by'. I
> understand why that normally needs to be the case, but in theory, it would be ok
> here because they
> are actually the same values (in the select and group by) but just formatted
> differently. I just
> want the query to be grouped and ordered by month and year, but to be formatted
> differently in the
> output.
>
> Any ideas?
You could do something like the following. It works in 7.4, but I am not
sure if it is completely standard.
area=> select to_char(date_trunc('month',current_date), 'MM ')
area-> group by date_trunc('month',current_date);
to_char
-
04 2004
(1 row)
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Formatting Functions and Group By
Terry Brick <[EMAIL PROTECTED]> writes:
> I'm porting a bunch of queries from MySQL to Postgres 7.4 and am having a problem
> with one
> particular area. For example, a query like this works in MySQL:
> select
> to_char(myCol,'Mon YY')
> from
> myTable
> group by
> to_char(myCol,'MM ')
> order by
> to_char(myCol,'MM ')
Ah, good ol' MySQL :-( ... let the user do what he wants whether the
result is well defined or not ...
I'd suggest doing the grouping/ordering numerically rather than
textually. For instance,
select
to_char(date_trunc('month', myCol), 'Mon YY')
from
myTable
group by
date_trunc('month', myCol)
order by
date_trunc('month', myCol);
Now this assumes you really want a time-based ordering, which the quoted
example doesn't give --- you've got month sorting to the left of year,
is that really what you want? If it is then you'd need to go
group by
date_trunc('month', myCol)
order by
to_char(date_trunc('month', myCol), 'MM ')
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] trigger/for key help
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > Is there a reason postgres goes out of its way to pick names that > > will be harder to work with than necessary? > > If we use ordinary identifiers for system-generated names then we will > be infringing on user name space --- ie, there's a potential for > conflict. I suppose we could use long randomly-generated names like > ewjncm343cnlen, but are those really easier to work with? I don't see an unseverable link between "user name space" and "identifiers that don't need to be quoted". Mixed case names for instance seem like perfectly good user name space identifiers. Postgres could just as easily say "the system reserves all identifiers starting with $" and still not require quoting $. > I think a more useful approach is to treat it as a documentation > problem. Perhaps an example in the ALTER TABLE man page would help. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Formatting Functions and Group By
Thank you both for your responses. That's just what I needed and thanks for
catching my
mistake Tom. And may I say that I am VERY happy to be moving to Postgres. The lack
of a native
Win32 version was thing only thing holding us back from Postgres previously.
I think this is the only kind of query I will have had difficulty porting. I'm
looking forward to
ditching MySQL and never looking back!! :)
--- Tom Lane <[EMAIL PROTECTED]> wrote:
>
> Ah, good ol' MySQL :-( ... let the user do what he wants whether the
> result is well defined or not ...
>
> I'd suggest doing the grouping/ordering numerically rather than
> textually. For instance,
>
> select
> to_char(date_trunc('month', myCol), 'Mon YY')
> from
> myTable
> group by
> date_trunc('month', myCol)
> order by
> date_trunc('month', myCol);
>
> Now this assumes you really want a time-based ordering, which the quoted
> example doesn't give --- you've got month sorting to the left of year,
> is that really what you want? If it is then you'd need to go
>
> group by
> date_trunc('month', myCol)
> order by
> to_char(date_trunc('month', myCol), 'MM ')
>
> regards, tom lane
__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] trigger/for key help
Greg Stark <[EMAIL PROTECTED]> writes: >>> Is there a reason postgres goes out of its way to pick names that >>> will be harder to work with than necessary? > I don't see an unseverable link between "user name space" and "identifiers > that don't need to be quoted". Mixed case names for instance seem like > perfectly good user name space identifiers. Sure, but they still have to be quoted. The issue here was specifically about whether the identifiers have to be quoted or not. > Postgres could just as easily say "the system reserves all identifiers > starting with $" and still not require quoting $. At this point identifiers starting with $ are a completely lost cause ;-). We have the parameter notation $n conflicting if the next character is a digit, and the dollar-quote mechanism conflicting with any other second character, and both of these are significantly more useful than the ability to have unquoted IDs starting with $ would be. So that's a dead end. I think if we wanted to change the default assignment of constraint names we'd just go with ordinary identifiers that we hope won't conflict with names the user picks. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] function returning array
I am trying to return an array from a function and don't seem to be having
luck. The function seems to work fine, but if I do assignment to an array
variable, I get null in the array elements
DECLARE
results varchar[];
tmpv varchar;
BEGIN
-- now call func that returns varchar[]
results := parseString(''abc,def,ghi'','','');
tmpv := results[1];
RAISE NOTICE '' tmpv = % '',tmpv;-- tmpv will be null.
END;
I found reference to this sort of thing not working, but the reference was
from several years ago, so I'm not sure if that is still the case or if I am
doing something wrong.
Do I need to go with returning a set instead?
Dennis S
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] function returning array
"Dennis" <[EMAIL PROTECTED]> writes:
> I am trying to return an array from a function and don't seem to be having
> luck.
Seems to work for me ... what PG version are you using?
regression=# create function parseString() returns varchar[] as
regression-# 'begin return \'{abc,def,ghi}\'; end' language plpgsql;
CREATE FUNCTION
regression=# select parseString();
parsestring
---
{abc,def,ghi}
(1 row)
regression=# create function foo() returns varchar as '
regression'# declare
regression'# results varchar[];
regression'# tmpv varchar;
regression'# begin
regression'# results := parseString();
regression'# tmpv := results[1];
regression'# RAISE NOTICE '' tmpv = % '',tmpv;
regression'# return tmpv;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo();
NOTICE: tmpv = abc
foo
-
abc
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 3: 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] function returning array
Dennis wrote:
I am trying to return an array from a function and don't seem to be
having luck. The function seems to work fine, but if I do assignment to
an array variable, I get null in the array elements
DECLARE
results varchar[];
tmpv varchar;
BEGIN
-- now call func that returns varchar[]
results := parseString(''abc,def,ghi'','','');
tmpv := results[1];
RAISE NOTICE '' tmpv = % '',tmpv;-- tmpv will be null.
END;
How is parseString() defined? What Postgres version?
Joe
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
