Re: [SQL] new serial type
Den lørdag 13. januar 2001 11:42 skrev Rolf Johansson: > Is there some function to change the serial type to, instead of reading > a next-value, read the maxium value in a column and then add x? If, for You can use setval(sequence, number) and MAX(number) in combination. -- Kaare Rasmussen--Linux, spil,--Tlf:3816 2582 Kaki Datatshirts, merchandize Fax:3816 2501 Howitzvej 75 Åben 14.00-18.00Email: [EMAIL PROTECTED] 2000 FrederiksbergLørdag 11.00-17.00 Web: www.suse.dk
Re: [SQL] pl/pgsql Limits
Najm Hashmi wrote: > Hi All, > As it is known that any funtion, written in pl/pgsql, can only > retrun one tuple. I am just wondering it were true as well for function > written in C language. I need to write few function that will retrun > mulitiple rows satsifying a certain set of conditions. Where I can get > some examples. > Tahnks in advance. > Najm Since the language handler is written in C, if it would've been possible (or make sense WRT the capabilities of the parser/planner/executor) I had done it for PL/pgSQL during initial creation of the language. We plan to tackle the problem for v7.2. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] pl/pgsql Limits
Jan Wieck <[EMAIL PROTECTED]> writes: >> As it is known that any funtion, written in pl/pgsql, can only >> retrun one tuple. I am just wondering it were true as well for function >> written in C language. I need to write few function that will retrun >> mulitiple rows satsifying a certain set of conditions. > We plan to tackle the problem for v7.2. It is possible for a C function to return a set (ie, multiple values returned over successive calls) as of 7.1; it's even documented, see src/backend/utils/fmgr/README. And you can return a tuple if you know how (this part is not documented, but you can crib it from the SQL-function support in backend/executor/functions.c). The real problem is that the rest of the system doesn't let you *do* anything very useful with either set-valued or tuple-valued functions. This is what we need to address in future releases. Ideally I think a function returning sets and/or tuples should be treated as a table source, so that you'd write ... FROM function(args) AS alias, ... regards, tom lane
[SQL] how to select a time frame on timestamp rows.
Hello, today i was trying to perform a query on a database using a time stamp field, i need to get all records which belong to year 2000, month 11, is there any other way to doit, or is this the pgsql way? , actually i'm using a query like this: select User_Name from tbacct where acct_timestamp like '2000-11%' group by User_Name; on MySQL i use this select User_Name from tbAcct where month (Acct_Timestamp) = 11 group by User_Name; (on mysql i was't worried about year yet) is anything like the month function from mysql on pg-sql? sorry if the question is to obvious, but i was reading the docs and can't find an answer. Regards!
Re: [SQL] how to select a time frame on timestamp rows.
[EMAIL PROTECTED] writes: > today i was trying to perform a query on a database using a time stamp > field, i need to get all records which belong to year 2000, month 11, > is there any other way to doit, or is this the pgsql way? , actually > i'm using a query like this: > select User_Name from tbacct where acct_timestamp like '2000-11%' group > by User_Name; select user_name from tbacct where extract(month from acct_timestamp) = 11 ... (SQL compliant) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Re: how to select a time frame on timestamp rows.
Peter Eisentraut wrote: Thanks Peter for the answer, so i assume i can also do select user_name from tbacct where extract(month from acct_timestamp) = 11 and extract(year from acct_timestamp) = 2000 ... > > [EMAIL PROTECTED] writes: > > > today i was trying to perform a query on a database using a time stamp > > field, i need to get all records which belong to year 2000, month 11, > > is there any other way to doit, or is this the pgsql way? , actually > > i'm using a query like this: > > select User_Name from tbacct where acct_timestamp like '2000-11%' group > > by User_Name; > > select user_name from tbacct where extract(month from acct_timestamp) = 11 ... > > (SQL compliant) > > -- > Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[SQL] Using INDEX on date/time values // Re: how to select a time frame on timestamp rows.
Hi,
Peter Eisentraut schrieb:
>
> > today i was trying to perform a query on a database using a time stamp
> > field, i need to get all records which belong to year 2000, month 11,
[...]
> select user_name from tbacct where extract(month from acct_timestamp) = 11 ...
is there any way of using an index for selecting some rows, e.g.
selecting all data from one month?
Explain sais, that the isn't used!
blaster=# explain select id from forum_data where extract(month from
date) = 1;
NOTICE: QUERY PLAN:
Seq Scan on forum_data (cost=0.00..59.74 rows=3 width=4)
also, I didn't found documentation about "extract" in the PG docs. ups!
;) wher is it, any hints?
In the mailing list archives I found the following hint:
select * from t1
where d >= (date_trunc('month', timestamp 'today')
- interval '1 month')
and d < date_trunc('month', timestamp 'today');
it also dosn't use index according to explain ...
My test table has ~350 rows.
Is it possible to use indexes?
or is it better to use an indexed int-field with unixtime? (int8!)
Thanks & Ciao
Alvar
--
Alvar C.H. Freude | [EMAIL PROTECTED]
Demo: http://www.online-demonstration.org/ | Mach mit!
Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit
Blast-EN: http://www.a-blast.org/ | Blast/english
