[SQL] distinct date problem
Hi, I'm having difficulty figuring this one out: I have a table with record entries which all have a date attribute, looking something like '2000-11-11'. What I want from my data base is a list of all the years that are present in this attribute in the relation. So for example, if I had a couple of records like: 2000-11-05 1988-02-09 1999-10-06 2001-08-08 1999-09-27 2001-04-04 I would want the query to return: 2000-11-05 1988-02-09 1999-10-06 2001-08-08 So that I get only the distict years. How does one go about this? Thanks, Lotus
[SQL] Using a messaging service....
Hello, I'm trying to use a java messaging service so I can post a message whenever my table has an update. Java API ^ | | Trigger update Java messagging
Re: [SQL] sum(bool)?
Olaf Marc Zanger wrote: >hi there, > >i want to add up the "true" values of a comparison like > >sum(a>b) > >it just doesn't work like this > >any workaround? select count(*) where a > b; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But God commendeth his love toward us, in that, while we were yet sinners, Christ died for us." Romans 5:8
Re: [SQL] distinct date problem
This should work:
SELECT distinct(date_part('year', )) FROM <>;
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
On Sun, 25 Feb 2001, Lotus118 wrote:
> Hi,
> I'm having difficulty figuring this one out:
> I have a table with record entries which all have a date attribute, looking
> something like '2000-11-11'. What I want from my data base is a list of
> all the years that are present in this attribute in the relation. So for
> example, if I had a couple of records like:
> 2000-11-05
> 1988-02-09
> 1999-10-06
> 2001-08-08
> 1999-09-27
> 2001-04-04
> I would want the query to return:
> 2000-11-05
> 1988-02-09
> 1999-10-06
> 2001-08-08
> So that I get only the distict years.
> How does one go about this?
> Thanks,
> Lotus
>
[SQL] Function to return recordset
Hi, I need help! How can I get either sql or plpgsql function to return recordset? Thanks, Qiqi
Re: [SQL] Function to return recordset
From: "Qiqi Dong" <[EMAIL PROTECTED]>
> Hi,
>
> I need help! How can I get either sql or plpgsql function to return
> recordset?
>
> Thanks,
> Qiqi
If you have a table foo you can define your function with "returns foo" but
you cannot use this like:
select foo_function('x');
At present it is only useful for use by other functions (I believe). I think
Tom said this is on the developer todo list though, so sometime after 7.1 it
should be possible.
- Richard Huxton
Re: [SQL] conversion
It looks like you have some rows for pyear which do
not convert cleanly into a number like ' '. What
do you want it to do in such cases?
On Sun, 25 Feb 2001, Ken Kline wrote:
> follow up
> actually the destination column is defined
> as a numeric(4)
>
> the following are the statements again with there error messages:
>
> SELECT pseason, to_number(pyear,'') from temp;
> ERROR: Bad numeric input format ' '
>
> SELECT pyear::int from temp;
> ERROR: Cannot cast type 'varchar' to 'int4'
>
>
>
>
> Ken Kline wrote:
>
> > Hello,
> > another brain twister, at least for me...
> > i have a table of varchar and one of the values I want
> > to insert into another table, one of the columns is
> > defined as INTEGER in destination table, column...
> > and none of these statements seem to work
> >
> > INSERT INTO pledge_classes (semester, year)
> > SELECT pseason, to_number('pyear','') from temp;
> >
> > INSERT INTO pledge_classes (semester, year)
> > SELECT pseason, pyear::integer from temp;
> >
> > INSERT INTO pledge_classes (semester, year)
> > SELECT pseason, pyear::numeric(4) from temp;
>
Re: [SQL] Function to return recordset
It would be nice to have this feature in next release. I cannot port my
applications to PostgreSQL without it. All my data objects depend on
recordset returned from stored procedures. Could someone consider it for
7.1? Thanks.
- Original Message -
From: "Richard Huxton" <[EMAIL PROTECTED]>
To: "Qiqi Dong" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, February 26, 2001 9:39 AM
Subject: Re: [SQL] Function to return recordset
> From: "Qiqi Dong" <[EMAIL PROTECTED]>
>
> > Hi,
> >
> > I need help! How can I get either sql or plpgsql function to return
> > recordset?
> >
> > Thanks,
> > Qiqi
>
> If you have a table foo you can define your function with "returns foo"
but
> you cannot use this like:
>
> select foo_function('x');
>
> At present it is only useful for use by other functions (I believe). I
think
> Tom said this is on the developer todo list though, so sometime after 7.1
it
> should be possible.
>
> - Richard Huxton
>
Re: [SQL] conversion
here you go, thanks in advance, ken
Table "temp"
Attribute |Type | Modifier
---+-+--
pseason | varchar(15) |
pyear | varchar(5) |
adx=# \d pledge+ _classe4s s
Table "pledge_classes"
Attribute | Type | Modifier
---+--+--
pc_id | integer | not null default nextval('pc_seq'::text)
semester | varchar(6) |
year | numeric(4,0) |
pc_letter | varchar(20) |
pc_name | varchar(50) |
Index: pc_pk
adx=# \q
bash-2.04$
Script done on Mon Feb 26 11:42:35 2001
Christopher Sawtell wrote:
> On Mon, 26 Feb 2001 17:11, Ken Kline wrote:
> > follow up
> > actually the destination column is defined
> > as a numeric(4)
>
> Could you to a \d on each of the tables and tell us the results.
>
> [ ... ]
>
> --
> Sincerely etc.,
>
> NAME Christopher Sawtell
> CELL PHONE 021 257 4451
> ICQ UIN45863470
> EMAIL csawtell @ xtra . co . nz
> CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
>
> -->> Please refrain from using HTML or WORD attachments in e-mails to me
> <<--
[SQL] Urgent help
Tom, I think one of system file has been crupted. I tried to drop some user then db is hangged there forever, so kill that session, when I relogin and type urldb=# \z categories NOTICE: get_groname: group 2 not found The connection to the server was lost. Attempting reset: Failed. !# and new user added cannot retrive data from any table. what I can do?? Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Tom Lane wrote: > Jie Liang <[EMAIL PROTECTED]> writes: > > My choice: > > if involving a lot of regular expressions, pl/Perl is better; > > if involving a lot of SQLs or other functions(or store procedures), > > then pl/pgsql is better. > > Also consider pltcl, which has pretty nearly perl-equivalent regexp > support, and can do queries too. Besides which it's easier to build/ > install than plperl. > > It's a shame that plperl doesn't yet have support for making queries. > It hasn't really progressed much past the proof-of-concept stage IMHO, > but no one is working on it :-( > > regards, tom lane >
[SQL] sysfile cruptted?
Tom,
I even cannot drop a user also,
urldb=# drop user sarah;
ERROR: DROP USER: group "manager" does not exist
urldb=#
urldb=# select * from pg_group;
groname | grosysid | grolist
-+--+
manager |5 | {1041,45,21}
engineering |3 | {1041,1042,21,23,42,22}
generic |4 | {1009,1044,36,1045,1043}
analysts|2 | {1048,1015,1004,1012,1014,45,1047}
urldb=# select * from pg_user where usename='sarah';
usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
passwd | valuntil
-+--+-+--+--+---+--+--
sarah | 1047 | f | f| f| f |
|
but user sarah is not a memeber of manager at all!!
What I can do??
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
On Wed, 21 Feb 2001, Tom Lane wrote:
> Jie Liang <[EMAIL PROTECTED]> writes:
> > My choice:
> > if involving a lot of regular expressions, pl/Perl is better;
> > if involving a lot of SQLs or other functions(or store procedures),
> > then pl/pgsql is better.
>
> Also consider pltcl, which has pretty nearly perl-equivalent regexp
> support, and can do queries too. Besides which it's easier to build/
> install than plperl.
>
> It's a shame that plperl doesn't yet have support for making queries.
> It hasn't really progressed much past the proof-of-concept stage IMHO,
> but no one is working on it :-(
>
> regards, tom lane
>
Re: [SQL] Urgent help
another weired thing is, when I: select * from pg_group; it takes about 2 minites to return, it took just a tick before, it seems that some sys tables has been locked for a timeout. Thanks. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com
[SQL] DLookup('field', 'table', ['condition'])
Hi,
I'm trying to create Domain Aggregate function that mimic Access' Dlookup
function, but without any luck, after digging (i think) all the docs
don't have the solution yet.
Syntax:
DLookup ('field', 'table|view', ['condition'])
where:
field -- column name or calculation
table|view --
condition (optional) -- SQL WHERE condition without the
WHERE keyword
If the query returns multiple rows then it will only get the
topmost column.
If the query retuns 0 rows then NULL would be returned
An example argument to the function would be
DLookup ('id', 'student', 'name=\'Bill Gates\'') --> '2001-432'
For now, I use some workaround for this, and it would be easier for me if
this function is present.
Hope u guys can help ;). Thank u in advance
Herbert
--
MSU - Iligan Institute of Technology
Iligan City, Philippines
Re: [SQL] DLookup('field', 'table', ['condition'])
On Tue, 27 Feb 2001, Herbert Ambos wrote:
> I'm trying to create Domain Aggregate function that mimic Access' Dlookup
> function, but without any luck, after digging (i think) all the docs
> don't have the solution yet.
>
> Syntax:
>
> DLookup ('field', 'table|view', ['condition'])
>
> where:
> field -- column name or calculation
> table|view --
> condition (optional) -- SQL WHERE condition without the
> WHERE keyword
>
> If the query returns multiple rows then it will only get the
> topmost column.
> If the query retuns 0 rows then NULL would be returned
>
>
> An example argument to the function would be
>
> DLookup ('id', 'student', 'name=\'Bill Gates\'') --> '2001-432'
Is this supposed to give effectively the same result as the subquery
(select student.id where name='Bill Gates' limit 1)? I don't think
that subquery is supported in 7.0, but will be in 7.1 along with
EXECUTE for plpgsql which should let you build a query out of the
parts you give it. I think you'd possibly be able to do this in 7.0
using pltcl but I don't know tcl so I can't help there.
