[SQL] How do i return a dataset from a stored procedure
I can't figure out how to return a group of rows from a function written in plpgsql (or plsql for that matter). The only way i have seen it done is with a setof table return value in sql. But since the query was a single select anyway i don't see the point of this. Is there a way of creating a temporary table in a procedure and using it as the output? thanks ---(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] How do i return a dataset from a stored procedure
On 5 Jul 2002, teknokrat wrote: > I can't figure out how to return a group of rows from a function > written in plpgsql (or plsql for that matter). The only way i have > seen it done is with a setof table return value in sql. But since the > query was a single select anyway i don't see the point of this. Is > there a way of creating a temporary table in a procedure and using it > as the output? In 7.2 you can return open cursors from plpgsql functions. You can make temp tables in a function, but you have to be careful to make sure that you don't conflict (what happens if the user calls your function twice) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How do i return a dataset from a stored procedure
On Fri, Jul 05, 2002 at 01:32:33AM -0700, teknokrat wrote: > I can't figure out how to return a group of rows from a function > written in plpgsql (or plsql for that matter). The only way i have > seen it done is with a setof table return value in sql. But since the > query was a single select anyway i don't see the point of this. Is > there a way of creating a temporary table in a procedure and using it > as the output? Search the list archives (through groups.google.com - it's comp.databases.postgresql) or see the developer docs for PL/pgSQL. You can achive that effect by returning a cursor in PG 7.2 (the section on returning cursors is ommitted from the current docs in www.postgresql.org/idocs, so you need to look at the developer docs - see www.us.postgresql.org). -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Go straight to the docs. Do not pass GO. Do not collect $200! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] i18n in pgSQL
Hi, I am working on an application intended for multilingual users. I have learnt from a source that I shall define double-byte data type such as nvarchar and ntext in DB. I don't see these data type in the pgsql online document. I have tried to create a table with the data type. The nvarchar is accepted, but not ntext. Also, I remember I can configure pgsql for unicode in Linux. I can't find what, where, and how to configure pgsql in my current setting: on cygwin. Currently, Chinese charaters can't be stored and retrieved properly. Thanks for your help. Vernon ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Possible Bug regarding temp tables (sql or psql?)
It works, but only in CVS, not in 7.2.X. Marking something as done in TODO only means it is done and will be in the _next_ release. Sorry. --- Mark Frazer wrote: > > > Bruce Momjian <[EMAIL PROTECTED]> [02/07/04 22:10]: > > > > TODO has: > > > > * Allow psql \d to show temporary table structure > > > > Looks like it works fine now with schemas: > > > > I will mark the TODO as done. > > It doesn't work with select into though: > > config=> select 5 into temp v_tmp ; > SELECT > config=> \d v_tmp > Did not find any relation named "v_tmp". > config=> select 4 into temp v_tmp ; > ERROR: Relation 'v_tmp' already exists > config=> select version() ; >version > - > PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 > > > -- > I heard one time you single-handedly defeated a hoard of rampaging somethings > in the something something system. - Fry > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Selecting data from a table created in another database...
Thanks. "Rudi Starcevic" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hi Ligia, > > When I need to do this I use a scripting language like PHP, Perl or > ColdFusion to > select from one db and insert into another. > As a matter of fact I had to do this exercise just yesturday. > I like it as I find I have much more freedom to gather data, organise it > then insert it. > It's easy to view the output of your selects on the screen and when > happy do the insert(s). > > Hope this helps. > Regards > Rudi Starcevic. > > Ligia Pimentel wrote: > > >I don't know if this can be done... > > > >In MSSQL Server I can access a table created in another database (on the > >same server, of course) by using the following syntaxis... > > > >select * from databasename..tablename where condition; > > > >Can I do this in postgres? > > > >I'm using version 7.2 on a redhat server... > > > >Thank you for your help... > > > >Ligia > > > > > > > > > > > > > > > > > >---(end of broadcast)--- > >TIP 6: Have you searched our list archives? > > > >http://archives.postgresql.org > > > > > > > > > > > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] algoritme
I have a question : I don't know if in a postgres database, it is interisting to create an index for a time stamp champ, Information : data are inerted in a chronological order. thanks Stephane -- Dubreuil Stephane [EMAIL PROTECTED] ---(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] how to use nextval()
Le ven 05/07/2002 à 14:03, [EMAIL PROTECTED] a écrit :
> Hello there
>
> I have an idea how to solve my problem on copy a record to a new one in the
> same table. To do so I need the next value for the primary key.
>
> Using :
>
> select nextval('pk_auftrag') as nextkey from auftrag;
>
> I get an error like : Relation pk_auftrag does not exist !
>
> Thanks for any help ... jr
Try to get the right name of the sequence.
By default, something like auftrag_auftrag_id_seq, if auftrag_id is the
serial primary key.
select nextval('auftrag_auftrag_id_seq') as nextkey;
S@S
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Can this be done with sql?
""Rajesh Kumar Mallah."" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hi , > > you can use GROUP BY , at the expense of adding one more column of SERIAL d= > ata type, > > say, > > select * from t_a limit 10; > access_log=3D# SELECT * from t_a limit 15; > > sno | value > -+--- >1 | 4533 >2 | 2740 >3 | 9970 > >4 | 6445 >5 | 2220 >6 | 2301 > >7 | 6847 >8 | 5739 >9 | 5286 > > 10 | 5556 > 11 | 9309 > 12 | 9552 > > 13 | 8589 > 14 | 5935 > 15 | 2382 > (15 rows) > > if you want avg for every third item you can use: > > access_log=3D# SELECT avg(value) from t_a group by (1+(sno-1)/3) limit 5;= > =20=20 > yes, thank you, that may help but unfortunately there are are few more problems to face. 1. I will need to select groups from anywhere in the table so i cannot assume that 1 will be the start number. They will be contigous however so i can use another query top get the start number but is it possible to do it with just one select? 2. I need to display not just aggregates but the first and last value in the group for two of the fields. I mean by this that i need opening_value(field1) and closing_value(field2). 3. If this needs to be done via stored procedure how do i get it to return a result set. I've tried setof record but it doesn't work. thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sql statement how to do ?
On Fri, 5 Jul 2002 09:03:38 + (UTC), [EMAIL PROTECTED] wrote: >INSERT INTO auftrag (SELECT * FROM auftrag where a_id = '12345'); > >The problem is, that the table auftrag has a primay key called pk_auftrag. >Do this I get an error regarding duplicate pk_auftrag. Is there a way to >spare pk_auftrag somehow ? Juerg, is a_id your primary key? My examples are based on this assumption, but if it is not, you still get the point, which is: You can't use SELECT * here. If you know the new a_id in advance: INSERT INTO auftrag (a_id, col2, col3, ...) SELECT '67890', col2, col3, ... FROM auftrag WHERE a_id = '12345'; If a_id is a serial or in any other way supplied automatically by a DEFAULT clause or a trigger: INSERT INTO auftrag (col2, col3, ...) SELECT col2, col3, ... FROM auftrag WHERE a_id = '12345'; HTH. Servus Manfred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Selecting data from a table created in another database...
hi Ligia, there was a posting to this list some time ago, about the same question. I just found it as I had the same question as you: http://archives.postgresql.org/pgsql-sql/1998-05/msg00091.php (in short: it is not possible in Postgres) the mail is from 1998, but I just tried to make a select using mydatabase.mytable as table name in a select statement and this does not work. Chantal ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Is Dropping a column "CHECK" constraint possible?
Hi Folks ,
can anyone please help?
i have a to drop a check contstraint from a column. eg
tradein_clients=# \d t_a
Table "t_a"
Column | Type | Modifiers
+-+---
company_id | integer |
exp| text|
imp| text|
Check constraints: "$1" (length(imp) > 1)
"aq" (length(imp) > 1)
Can i remove to contraints thru ALTER TABLE or similar commands.
I have read the docs for ALTER TABLE but the command described is
not working for me.
===
ALTER TABLE [ ONLY ] table
DROP CONSTRAINT constraint { RESTRICT | CASCADE }
Regards
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] how to use nextval()
Hello there
I have an idea how to solve my problem on copy a record to a new one in the
same table. To do so I need the next value for the primary key.
Using :
select nextval('pk_auftrag') as nextkey from auftrag;
I get an error like : Relation pk_auftrag does not exist !
Thanks for any help ... jr
__
PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland
internet : www.pup.ch
phone : +4141 790 4040
fax : +4141 790 2545
mobile: +4179 211 0315
__
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Possible Bug regarding temp tables (sql or psql?)
Bruce Momjian <[EMAIL PROTECTED]> [02/07/04 22:10]: > > TODO has: > > * Allow psql \d to show temporary table structure > > Looks like it works fine now with schemas: > > I will mark the TODO as done. It doesn't work with select into though: config=> select 5 into temp v_tmp ; SELECT config=> \d v_tmp Did not find any relation named "v_tmp". config=> select 4 into temp v_tmp ; ERROR: Relation 'v_tmp' already exists config=> select version() ; version - PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 -- I heard one time you single-handedly defeated a hoard of rampaging somethings in the something something system. - Fry ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Boolean to int
Le jeu 04/07/2002 à 23:32, Arjen van der Meijden a écrit : > How about this hint in the postgresql-manual: > >"Tip: Values of the boolean type cannot be cast directly to other > types (e.g., CAST (boolval AS integer) does not work). This can be > accomplished using the CASE expression: CASE WHEN boolval THEN 'value if > true' ELSE 'value if false' END. See also Section 4.12. " > > For more information: > http://www.postgresql.org/idocs/index.php?datatype-boolean.html > and > http://www.postgresql.org/idocs/index.php?functions-conditional.html I tried that : CREATE RULE boolean_return AS ON SELECT TO DOCUMENT DO INSTEAD SELECT document_id, workflow_id, type_document_id, image_id, theme_id, document_version, document_surtitre, document_titre, document_chapeau, document_synthese, document_corps, document_pdf, document_date_creation, document_mot_clef, document_online, document_valid, CASE document_online WHEN TRUE THEN 1 WHEN FALSE THEN 0 END, CASE document_valid WHEN TRUE THEN 1 WHEN FALSE THEN 0 END FROM document; And, that doesn't work either... psql:cnambo_proc_stock.sql:76: ERROR: select rule's target list has too many entries > -- _Stéphane SCHILDKNECHT___ | AurorA-SAS 69-71, Av. Pierre Grenier 92100 BOULOGNE | | Tel : 01.58.17.03.20 Fax : 01.58.17.03.21 | | mailto:[EMAIL PROTECTED] - ICQ : 142504394 | | "Free Markets have taught that innovation is best when | | ideas flow freely." Adam Smith | |_| ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How do i return a dataset from a stored procedure
Roberto Mello wrote: > On Fri, Jul 05, 2002 at 01:32:33AM -0700, teknokrat wrote: > > I can't figure out how to return a group of rows from a function > > written in plpgsql (or plsql for that matter). The only way i have > > seen it done is with a setof table return value in sql. But since the > > query was a single select anyway i don't see the point of this. Is > > there a way of creating a temporary table in a procedure and using it > > as the output? > > Search the list archives (through groups.google.com - it's > comp.databases.postgresql) or see the developer docs for PL/pgSQL. You > can achive that effect by returning a cursor in PG 7.2 (the section on > returning cursors is ommitted from the current docs in > www.postgresql.org/idocs, so you need to look at the developer docs - see > www.us.postgresql.org). 7.3 will have better docs on using it but you can see them now in the developers docs: http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html See the bottom of that page for examples. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is Dropping a column "CHECK" constraint possible?
> can anyone please help? > i have a to drop a check contstraint from a column. eg > > tradein_clients=# \d t_a >Table "t_a" >Column | Type | Modifiers > +-+--- > company_id | integer | > exp| text| > imp| text| > Check constraints: "$1" (length(imp) > 1) >"aq" (length(imp) > 1) > > Can i remove to contraints thru ALTER TABLE or similar commands. > I have read the docs for ALTER TABLE but the command described is > not working for me. This should work, so long as you're using postgres 7.2+ ALTER TABLE "t_a" DROP CONSTRAINT "aq" RESTRICT; Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is Dropping a column "CHECK" constraint possible?
Yup it did!. Thanks a ton, it was there in the ALTER TABLE documentation i overlooked :-( regds mallah. On Saturday 06 July 2002 10:05, Christopher Kings-Lynne wrote: > > can anyone please help? > > i have a to drop a check contstraint from a column. eg > > > > tradein_clients=# \d t_a > >Table "t_a" > >Column | Type | Modifiers > > +-+--- > > company_id | integer | > > exp| text| > > imp| text| > > Check constraints: "$1" (length(imp) > 1) > >"aq" (length(imp) > 1) > > > > Can i remove to contraints thru ALTER TABLE or similar commands. > > I have read the docs for ALTER TABLE but the command described is > > not working for me. > > This should work, so long as you're using postgres 7.2+ > > ALTER TABLE "t_a" DROP CONSTRAINT "aq" RESTRICT; > > Chris -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
