[SQL] postgresql and the industry
Hello, I am working on a bibliography or document concerning examples of use of PostgreSQL in the industrial circle. I visited the official site but there is not a lot of information. Thank you and good day. Walid DIB ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Help required on update query
Hi Christoph,Could you please let me know the syntax for set newCol=oldCol, (This is reference to your mail pasted below)RegardsVikas Sawant[EMAIL PROTECTED]> > http://fts.postgresql.org/db/mw/msg.html?mid=1071582 > resp. > > You can rename your old column, create a new column varchar(100), update > ... set newCol=oldCol, drop oldCol and rename newCol to oldCol > See ALTER TABLE doc for this. > Ok, this is the accurate solution to minimize side effects. Thanks to Robert and Andreas for pointing this out. Regards, Christoph
[SQL] PostgreSQL and industry
Hello, I am searching for big industrial project using PostgreSQL as a database system manager. Can anyone give references or bibliograpghy other than the official postgres site? thank you and sorry for my english. DIB Walid Eleve Ingénieur en Technologies de l'information pour la santé Ecole Polytechnique Universitaire de Grenoble Accédez au courrier électronique de La Poste : www.laposte.net ; 3615 LAPOSTENET (0,34/mn) ; tél : 08 92 68 13 50 (0,34/mn)" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] How can I use a name of table in attribute of function, I try PREPARE...
I can do that on PostgreSQL 7.3 ?? Is it possible to do that ? or must I upgrade my server ?? PREPARE q1(text) FROM SELECT * FROM pg_tables WHERE tablename = $1 ; EXECUTE q1 USING 'client' ; DEALLOCATE q1 ; ---(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] Upgrade 7.2.3 -> 7.3 or more
I have to use PREPARE statement, I have just read an article that this function is not implemented in 7.3 version ? is it correct or not ? If I must tu upgrade my server : I install different languages in databases, must I reinstall all of them after upgraded ? Ben ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] tablename as attribute in pgplsql
Thx to Staphan Finally I find a solution : I have tables with different languages and I must find the correct languages : DECLARE chaine VARCHAR := 'category'; col_return VARCHAR := 'id_category_unique' ; col_id VARCHAR := 'id_category' ; col_value INTEGER := 5 ; lang_id INTEGER := 3 ; r RECORD; str BIGINT; BEGIN FOR r in execute 'SELECT ' || col_return ||' AS info FROM ' || chaine || ' WHERE ' || col_id || ' = ' || col_value || ' AND id_language = ' || lang_id loop str := r.info; END LOOP; RETURN str; END; Stephan Szabo a écrit: On Tue, 27 May 2003, [ISO-8859-1] Beno?t Bournon wrote: I try to do that : DECLARE chaine VARCHAR := 'client' ; str BIGINT ; BEGIN SELECT id_user into str FROM client WHERE id_user = 112 ; EXECUTE 'SELECT id_user into str FROM chaine WHERE id_user = 112' ; RETURN str ; END ; It would be (doubling quotes because the function body is in quotes) EXECUTE ''SELECT id_user into str FROM '' || chaine || '' WHERE id_user = 112'' except that IIRC you can't do select into str from execute, instead (as I alluded to below) I think you need to say something wacky like 'DECLARE chaine VARCHAR := 'client'; r RECORD; str BIGINT; BEGIN FOR r in execute ''SELECT id_user FROM '' || chaine || '' WHERE id_user = 112'' loop str := r.id_user; END LOOP; RETURN str; END;' Stephan Szabo a ?crit: On Thu, 22 May 2003, [ISO-8859-1] Beno?t Bournon wrote: DECLARE usertablename ALIAS FOR $1 ; userid ALIAS FOR $2 ; id_lang BIGINT ; var VARCHAR(200) ; BEGIN var := 'client' ; SELECT id_language INTO id_lang FROM client WHERE id_user = userid ; IF NOT (id_lang = 1 OR id_lang = 3) THEN id_lang = 1 ; END IF ; RETURN id_lang ; END ; I want to use client table as a variable How could I do Look at the documentation for EXECUTE. Unfortunately using EXECUTE into a variable is a little complicated right now AFAIR because you can't using select ... INTO variable inside the execute, but instead need to use something like for in execute loop. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Index scan never executed?
Hi,
This may make the query faster.
try to split the query into two parts:
first fetch all list-id's into a temp table (where cr.project_id = '55' and
cr.start_time between '4/4/2003 0:0' and now())
then, join the temp table on lists (where l.list_of_lists_id = '691').
Morten
-Opprinnelig melding-
Fra: Chad Thompson [mailto:[EMAIL PROTECTED]
Sendt: 29. mai 2003 19:20
Til: pgsql-sql
Emne: [SQL] Index scan never executed?
I have never been very good at reading these query plans, but I have a bit
of a problem w/ my query. So any help is appreciated.
The query is fairly self explanitory i think. 2 tables, call_results ( 6.5
Million records ) and lists ( 11 Million records )
weblink=# explain analyze
weblink-# select count(*) as count
weblink-# from call_results cr join lists l on cr.list_id = l.id
weblink-# where cr.project_id = '55'
weblink-# and cr.start_time between '4/4/2003 0:0' and now()
weblink-# and l.list_of_lists_id = '691';
QUERY PLAN
-
Aggregate (cost=2519.58..2519.58 rows=1 width=16) (actual
time=110715.45..110715.46 rows=1 loops=1)
-> Nested Loop (cost=0.00..2519.58 rows=1 width=16) (actual
time=110715.43..110715.43 rows=0 loops=1)
-> Index Scan using start_time_idx on call_results cr
(cost=0.00..2021.00 rows=164 width=8) (actual time=110715.42..110715.42
rows=0 loops=1)
Index Cond: ((start_time >= '2003-04-04
00:00:00-07'::timestamp with time zone) AND (start_time <= now()))
Filter: (project_id = 55::bigint)
-> Index Scan using lists_pkey on lists l (cost=0.00..3.03 rows=1
width=8) (never executed)
Index Cond: ("outer".list_id = l.id)
Filter: (list_of_lists_id = 691::bigint)
Total runtime: 110747.58 msec
(9 rows)
The big thing I dont understand is why it tells me (never executed) on
lists_pkey.
I also dont see where all the time is being taken up. I thought that
(actual time=110715.42..110715.42) meant from millisecond this... TO
millisecond that, but that would mean that this index scan took no time. So
as you can see I am very confused. :-)
TIA for any suggestions on how to make this query faster.
Chad
PS I have run vacuum full and analyze as reciently as last night :-)
---(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
---(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] generic return for functions
Hi, this is my first time using mailing list. Someone over at tek-tips
suggested that I try here to see if someone can help me.
I am currently converting everything from SQL Server to PostgreSQL. This is
for an application that is going to support Oracle, SQL Server and
PostgreSQL at the same time. I have done a lot of the conversion already
but I am stuck on functions that returns parts of views or tables.
In SQL Server, you can create User Defined functions that returns type
"TABLE"... so then you can simply return the result of a select statement...
(e.g. return select * from mytable)
The problem is that I don't know if there is a way to do this in PostgreSQL.
My functions and stored procedures in SQL Server involves select statement
that gets columns from few views and tables.
I know I can create my own data type with all the columns that are going to
be returned, or get my function to return a type "record"... however, I
don't like both of these methods since some of my functions involves
returning a select statement using inner join and all that stuff... some
have like 30 columns
There seems to be another way to use refcursor but my application is in
ColdFusion... refcursor doesn't seem to work with it.
I know "returns setof record" and "returns setof my_own_datatype" work, but
I would like to know if there is a better way? Something that's like
"returns setof record" but without having to define all the columns when I
call the function? {i.e. without the need to do: select * from myfunction()
as (column1 type1, column2...);}
If there is such method? It will allow me to maintain the application much
easier and makes the conversion task much easier :)
Thanks.
/Danny
_
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] generic return for functions
I am afraid you are in exactly the same boat I am in. Coldfusion does
not recognize the refcursor and as far as I know, currently the only
way to return a result set that CF can read is to return a setof which
requires you to return a user defined data type or a record. Another
thing that causes me some minor grief is the fact that currently you
cannot have default values to function parameters, a feature we use a
lot.
Avi
On Saturday, May 31, 2003, at 23:02 America/Chicago, Danny Su wrote:
I am currently converting everything from SQL Server to PostgreSQL.
This is for an application that is going to support Oracle, SQL Server
and PostgreSQL at the same time. I have done a lot of the conversion
already but I am stuck on functions that returns parts of views or
tables.
In SQL Server, you can create User Defined functions that returns type
"TABLE"... so then you can simply return the result of a select
statement... (e.g. return select * from mytable)
The problem is that I don't know if there is a way to do this in
PostgreSQL.
My functions and stored procedures in SQL Server involves select
statement that gets columns from few views and tables.
I know I can create my own data type with all the columns that are
going to be returned, or get my function to return a type "record"...
however, I don't like both of these methods since some of my functions
involves returning a select statement using inner join and all that
stuff... some have like 30 columns
There seems to be another way to use refcursor but my application is
in ColdFusion... refcursor doesn't seem to work with it.
I know "returns setof record" and "returns setof my_own_datatype"
work, but I would like to know if there is a better way? Something
that's like "returns setof record" but without having to define all
the columns when I call the function? {i.e. without the need to do:
select * from myfunction() as (column1 type1, column2...);}
If there is such method? It will allow me to maintain the application
much easier and makes the conversion task much easier :)
--
Avi Schwartz
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] generic return for functions
Danny Su wrote:
I am currently converting everything from SQL Server to PostgreSQL.
This is for an application that is going to support Oracle, SQL Server
and PostgreSQL at the same time. I have done a lot of the conversion
already but I am stuck on functions that returns parts of views or tables.
[...snip description of SQL Server 2000 table-valued UDFs...]
I know "returns setof record" and "returns setof my_own_datatype" work,
but I would like to know if there is a better way? Something that's like
"returns setof record" but without having to define all the columns when
I call the function? {i.e. without the need to do: select * from
myfunction() as (column1 type1, column2...);}
If there is such method? It will allow me to maintain the application
much easier and makes the conversion task much easier :)
Sorry -- the answer is no. But I don't think Oracle will support what
you want either.
Joe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Upgrade 7.2.3 -> 7.3 or more
On Tue, 2003-05-27 at 08:44, Benoît Bournon wrote: > I have to use PREPARE statement, I have just read an article that this > function is not implemented in 7.3 version ? It's implemented, however how it works internally will change somewhat in 7.4. http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-prepare.html -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] generic return for functions
> thing that causes me some minor grief is the fact that currently you > cannot have default values to function parameters, a feature we use a > lot. The default value is used when the parameter is NULL or unprovided? fn(integer, integer, integer default 32) select fn(integer, integer); <- Third argument would be '32'? When PostgreSQL gets named parameters the above probably makes sense to add. A TODO item? -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
