Re: [SQL] error with mor than 1 sub-select

[Erik Jones]

Actually, it pointed you right to it.  Notice that exits is just before 
where the pointer was.


chrisj wrote:

thanks, stupid user error.

i guess the pointer on the error message led me astray


Michael Fuhr wrote:
  

On Tue, Aug 22, 2006 at 06:47:51PM -0700, chrisj wrote:


The first query works fine, but when a second sub-query is added I get a
syntax error...

psql protocal2 -U p2user << EOF1
select *
   from serv_res SR
   where serv_key = 10
 and not exists 
(select 1

  from reservation R
)
-- and not exits 
  

Does the real query have "exits" instead of "exists"?  I created
some test tables and fixed that typo and then both queries worked.

--
Michael Fuhr

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

   http://archives.postgresql.org





  



--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] SQL92 compliance

[Peter Eisentraut]

Am Mittwoch, 23. August 2006 03:40 schrieb Daniel CAUNE:
> Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92
> compliant?

No.  I have a patch at 

 
that fixes this at least for 7.4.  I don't think it works for newer versions, 
but it should give you an idea what is required to get it working.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

   http://archives.postgresql.org

Re: [SQL] SQL92 compliance

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Am Mittwoch, 23. August 2006 03:40 schrieb Daniel CAUNE:
>> Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92
>> compliant?

> No.  I have a patch at 
> 
>  
> that fixes this at least for 7.4.

I think it's a big stretch to say that that patch fixes it, since it
only allows an AS-less target expression to be c_expr rather than
a_expr as it ought to.

The problem is really insoluble given that we allow user-defined
postfix operators: is "SELECT x ~~ y" meant to be an infix operator
with arguments x and y, or a postfix operator with argument x and
a column label y?

When this has come up in the past, we've always concluded that
compliance with this not-very-well-thought-out detail of the spec
is not worth the price of giving up postfix operators.

Even if we were willing to do that, I think we'd also have to give
up using bison to generate the parser :-( because some constructs
would require more than one-token lookahead.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] The length of the sql query

[Emi Lu]

Hello,

Just curious to know whether postgresql has any length constraint about 
where part, such as



Query =
[
select col1, col2, ... coln
from table 1, table2,
where

constraint1 + constraint2 +constraintN
]

Is there any length arrange for the Query str such as 500M, 1G, etc? Or 
the query can be as long as it is.


Thanks a lot!


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] SQL92 compliance

[Scott Marlowe]

On Wed, 2006-08-23 at 12:40, Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Am Mittwoch, 23. August 2006 03:40 schrieb Daniel CAUNE:
> >> Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92
> >> compliant?
> 
> > No.  I have a patch at 
> > 
> >  
> > that fixes this at least for 7.4.
> 
> I think it's a big stretch to say that that patch fixes it, since it
> only allows an AS-less target expression to be c_expr rather than
> a_expr as it ought to.
> 
> The problem is really insoluble given that we allow user-defined
> postfix operators: is "SELECT x ~~ y" meant to be an infix operator
> with arguments x and y, or a postfix operator with argument x and
> a column label y?
> 
> When this has come up in the past, we've always concluded that
> compliance with this not-very-well-thought-out detail of the spec
> is not worth the price of giving up postfix operators.
> 
> Even if we were willing to do that, I think we'd also have to give
> up using bison to generate the parser :-( because some constructs
> would require more than one-token lookahead.

Would it be possible if we required postfix operators and related to be
inside parens?

select x ~~ y as yabba
OR
select (x ~~ y) yabba

Not that I'd want that.  I prefer it the way it is too.  Just more of an
intellectual exercise.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] All columns from table in a joined query

[MHahn]

I've been trying to figure out how to do the following:

Select schedule.* from schedule join meetingday on schedule.id = 
meetingday.scheduleid where sessionnumber = 165 group by schedule.* order by 
min(meetingday.date);

Is this possible in any way, or do I need to list each field of the schedule 
table to get all of them?

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] Deleting Functions

[Scott Petersen]

Folks,

I am using PGSQL do do all of my schema changes, but have run into a 
problem.  I need to be able to DROP all of the USER FUNCTIONS that are 
defined in a schema.  The problem is that I do not know the names of all 
of the functions and parameters.  I know that I could use '\df' and then 
use perl or some other scripting language to parse this, but I need to 
be able to perform this action on multiple platforms (without rewriting 
the process for each platform). 

I have written a script that incorporates '\df' and appropriate parsing 
to perform this action for LINUX and it works fine/lasts a long time.  I 
need to make this more transparent so it will run on all platforms (in 
particular windows).


I know that in the past (version 7) there was a way to drop the 
functions by directly accessing some of the system tables.  The 
question/problem is that we have updated to version 8.1 and need the 
same functionality.




Any help or directions would be greatly appreciated.

Thanks Scott.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] All columns from table in a joined query

[Sumeet]

You need to create a custom aggregate for this

CREATE AGGREGATE array_accum (

sfunc = array_append,

basetype = anyelement,

stype = anyarray,

initcond = '{}'

);


then use the field names in your query like this


select array_to_string(array_accum(field1 || '@' || field2),'#') as field_alias from table_name

records will be seperated by '#'
hope this helps.





On 8/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
I've been trying to figure out how to do the following:Select
schedule.* from schedule join meetingday on schedule.id =
meetingday.scheduleid where sessionnumber = 165 group by schedule.*
order by min(meetingday.date);Is this possible in any way, or do I need to list each field of the schedule table to get all of them?---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings-- Thanks,Sumeet AmbreMasters of Information Science Candidate,Indiana University.
On 8/23/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
I've been trying to figure out how to do the following:Select schedule.* from schedule join meetingday on 
schedule.id = meetingday.scheduleid where sessionnumber = 165 group by schedule.* order by min(meetingday.date);Is this possible in any way, or do I need to list each field of the schedule table to get all of them?
---(end of broadcast)---TIP 5: don't forget to increase your free space map settings-- Thanks,Sumeet Ambre
Masters of Information Science Candidate,Indiana University.

Re: [SQL] Deleting Functions

[Jim Buttafuoco]

Scott,

I use the following query with psql \o option.  Change the schema name from 
public to whatever.  I am sure you could put
this into a plpgsql function using execute as well.


Jim

\o drops.sql
select 'drop function ' || nspname || '.' || proname || '(' || 
pg_catalog.oidvectortypes(p.proargtypes) || ');' from
pg_proc p join pg_namespace b on (p.pronamespace=b.oid) where nspname='public';
\o
\!vi drops.sql # <
To: pgsql-sql@postgresql.org
Sent: Wed, 23 Aug 2006 13:29:12 -0600
Subject: [SQL] Deleting Functions

> Folks,
> 
> I am using PGSQL do do all of my schema changes, but have run into a 
> problem.  I need to be able to DROP all of the USER FUNCTIONS that are 
> defined in a schema.  The problem is that I do not know the names of all 
> of the functions and parameters.  I know that I could use '\df' and then 
> use perl or some other scripting language to parse this, but I need to 
> be able to perform this action on multiple platforms (without rewriting 
> the process for each platform).
> 
> I have written a script that incorporates '\df' and appropriate parsing 
> to perform this action for LINUX and it works fine/lasts a long time.  I 
> need to make this more transparent so it will run on all platforms (in 
> particular windows).
> 
> I know that in the past (version 7) there was a way to drop the 
> functions by directly accessing some of the system tables.  The 
> question/problem is that we have updated to version 8.1 and need the 
> same functionality.
> 
> Any help or directions would be greatly appreciated.
> 
> Thanks Scott.
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
--- End of Original Message ---


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

   http://archives.postgresql.org

Re: [SQL] All columns from table in a joined query

[Tom Lane]

[EMAIL PROTECTED] writes:
> I've been trying to figure out how to do the following:
> Select schedule.* from schedule join meetingday on schedule.id = 
> meetingday.scheduleid where sessionnumber = 165 group by schedule.* order by 
> min(meetingday.date);

I think what you're after is

select * from schedule where sessionnumber = 165
order by (select min(meetingday.date) from meetingday
  where schedule.id = meetingday.scheduleid);

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Deleting Functions

[Tom Lane]

"Jim Buttafuoco" <[EMAIL PROTECTED]> writes:
> select 'drop function ' || nspname || '.' || proname || '(' || 
> pg_catalog.oidvectortypes(p.proargtypes) || ');' from
> pg_proc p join pg_namespace b on (p.pronamespace=b.oid) where 
> nspname='public';

Seems like you could do that more easily with regprocedure:

select 'drop function ' || p.oid::regprocedure || ';' from
pg_proc p join pg_namespace b on (p.pronamespace=b.oid) where nspname='public';

[ tries it ... ] ... except that there's no regprocedure-to-text cast.
How annoying :-(

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] SQL92 compliance

[Peter Eisentraut]

Tom Lane wrote:
> I think it's a big stretch to say that that patch fixes it, since it
> only allows an AS-less target expression to be c_expr rather than
> a_expr as it ought to.

Well, it works for a useful subset.  I remember that some other database 
did not *allow* the AS, so there were porting troubles, but I don't 
remember the details.  I'm just saying that it's doable if it's a 
necessity for someone.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] SQL92 compliance

[Peter Eisentraut]

Scott Marlowe wrote:
> Would it be possible if we required postfix operators and related to
> be inside parens?
>
> select x ~~ y as yabba
> OR
> select (x ~~ y) yabba

That's pretty much what you get if you restrict the expression to 
c_expr.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 6: explain analyze is your friend