Re: [SQL] trigger/for key help

[Greg Stark]

Tom Lane [EMAIL PROTECTED] writes:

 Bret Hughes [EMAIL PROTECTED] writes:
  FWIW I tried to use alter table but could never get the parser to accept
  $1 as a constraint name.  I used single and double quotes as well as a
  lame attempt \$1.
 
 Hm, $1 works for me ...

Hm, this reminds me. When I was first learning this stuff I was stymied by the
same issue. It took me quite a while to figure out how to drop constraints
because of the quoting issue.

Of course now it seems obvious, but for someone just starting it adds another
roadblock. Is there a reason postgres goes out of its way to pick names that
will be harder to work with than necessary?

Or is it considered a good thing on the theory that if it's hard to reference
it's also hard to accidentally use such names in conflicting ways?

Perhaps names like _1 _2 ... would be easier to handle? 
Or perhaps making $ not require quoting would be helpful?

-- 
greg


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] cursors and for loops?

[Richard Huxton]

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

[Tom Lane]

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

[Terry Brick]

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

[Bruno Wolff III]

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

[Tom Lane]

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

[Greg Stark]

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

[Terry Brick]

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

[Tom Lane]

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

[Dennis]

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

[Tom Lane]

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

[Joe Conway]

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