Re: [SQL] Stored Procedure Problem
Hi, use this CREATE FUNCTION b_function() RETURNS int4 AS ' DECLARE an_integer int4; BEGIN select into an_integer emp_id from employee; return an_integer; END; ' LANGUAGE 'plpgsql'; --- Atul <[EMAIL PROTECTED]> escribió: > Hello, > > Atul Here, I have one problem while > accessing Database > Records Or Recordset from stored procedure. > Procedure is like this, > > CREATE FUNCTION b_function() RETURNS int4 AS ' > DECLARE >an_integer int4; > BEGIN >select emp_id from employee; >return an_integer; > END; > ' > LANGUAGE 'plpgsql'; > > I create the procedure . After that I run the > statement like > > Test=#> select b_function(); > > Error comes like > > Error: ERROR: SELECT query has no destination for > result data. > If you want to discard the results, > use PERFORM instead. > WARNING: Error occurred while executing PL/pgSQL > function b_function > WARNING: line 4 at SQL statement > > Also I use PERFORM statement. But still problem is > not resolved. > > Please Help me. I hope your kind co-operation. > > Thanks. > > Atul > Ahora podés usar Yahoo! Messenger desde tu celular. Aprendé cómo hacerlo en Yahoo! Móvil: http://ar.mobile.yahoo.com/sms.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Primary Key Help !
--- Waheed Rahuman <[EMAIL PROTECTED]> wrote: > Hi Greetings > My question is How many primary key i can assign in > a PostGresql Table > > Rowid| Parent1 | Parent2 > | Parent3 | Parent4 | > Parent5 | Parent6 | Parent7 >| Parent8 | Parent9 | > Parent10 | > > Here i want to make Parent1.Parent 10 as > Primary Key and ...this parent field i want to make > it more than 10 fieldsif i try to make primary > key more than 10 fields in ms access its say not > possible so please let me know how i can do that in > psql. or if there is an alternative way...i will be > glad Assuming that you have already created the table try doing the following: ALTER TABLE ADD CONSTRAINT PRIMARY KEY (parent1,parent2,parent10); Hope that helps, ludwig __ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] primary + foreign key
Hi, I want to create a table where the 2 columns are both primary as well as foreign key. What is the correct syntax for that? Thanks Huub ---(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] Getting the latest unique items
A.M. wrote: When I try to run the following query: select distinct on(student_gradedmaterial.id) student_gradedmaterial.id from coursesection_student,student_gradedmaterial WHERE gradedmaterialid=1 AND coursesection_student.studentid=student_gradedmaterial.studentid AND coursesectionid=1 and score is not null order by submittime desc; I get the following error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions No You have to change your sort order (as in error above). It should be: order by student_gradematerial.id asc, submittime desc; Tomasz Myrta ---(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] primary + foreign key
Huub wrote: Hi, I want to create a table where the 2 columns are both primary as well as foreign key. What is the correct syntax for that? Thanks Huub create table detail ( id1 integer, id2 integer, CONSTRAINT c1 PRIMARY KEY(id1,id2), CONSTRAINT c2 FOREIGN KEY(id1,id2) REFERENCES master ); Do you really need such detail table? You can just add columns to master table. Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] primary + foreign key
Hi, I suppose 'detail' is the table name? And what is master? Thanks Huub create table detail ( id1 integer, id2 integer, CONSTRAINT c1 PRIMARY KEY(id1,id2), CONSTRAINT c2 FOREIGN KEY(id1,id2) REFERENCES master ); Do you really need such detail table? You can just add columns to master table. Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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] primary + foreign key
Huub wrote: Hi, I suppose 'detail' is the table name? And what is master? Thanks Huub You should know that. Detail is a table you want to create and master is a table where foreign key points to. Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] primary + foreign key
Thanks for pointing out. I'm not that experienced in SQL yet; so detail could as well be some extra instruction and I've never seen 'references master' before. But I think I'll manage now. Thanks Tomasz Myrta wrote: Huub wrote: Hi, I suppose 'detail' is the table name? And what is master? Thanks Huub You should know that. Detail is a table you want to create and master is a table where foreign key points to. Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Performance Problems
Hi, I have performance problems with a huge database (there a 2 tables with 40 millions of records) and many users doing updates and queries on it. I 've perform severals VACUMM on the database with poor results. Each table have an unique index and I added other indexes to improve the performance. But when there are inserts (not too many) the performance fall. What can I do to improve the performace? I hear any opinion. Hector Iturre Ahora podés usar Yahoo! Messenger desde tu celular. Aprendé cómo hacerlo en Yahoo! Móvil: http://ar.mobile.yahoo.com/sms.html ---(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] Stored Procedure Problem
Hello, Atul Here, I have one problem while accessing Database Records Or Recordset from stored procedure. Procedure is like this, CREATE FUNCTION b_function() RETURNS int4 AS ' DECLARE an_integer int4; BEGIN select emp_id from employee; return an_integer; END; ' LANGUAGE 'plpgsql'; I create the procedure . After that I run the statement like Test=#> select b_function(); Error comes like Error: ERROR: SELECT query has no destination for result data. If you want to discard the results, use PERFORM instead. WARNING: Error occurred while executing PL/pgSQL function b_function WARNING: line 4 at SQL statement Also I use PERFORM statement. But still problem is not resolved. Please Help me. I hope your kind co-operation. Thanks. Atul…
Re: [SQL] Stored Procedure Problem
> CREATE FUNCTION b_function() RETURNS int4 AS ' > DECLARE >an_integer int4; > BEGIN >select emp_id from employee; >return an_integer; > END; > ' > LANGUAGE 'plpgsql'; > Try SELECT INTO an_integer emp_id from employee; Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Performance Problems
At 09:56 AM 12/12/2002 -0300, Héctor Iturre wrote: I 've perform severals VACUMM on the database with poor results. Have you done an ANALYZE? Which version of PG are you using? Can you send output from VACUUM VERBOSE ? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Performance Problems
> >I have performance problems with a huge database > (there a 2 tables with 40 millions of records) and > many users doing updates and queries on it. I 've > perform severals VACUMM on the database with poor > results. >Each table have an unique index and I added other > indexes to improve the performance. But when there are > inserts (not too many) the performance fall. >What can I do to improve the performace? I hear any > opinion. > Bear in mind, every index has to be updated when records are inserted. An unique one makes it even worse, because a search for duplicates must be performed. You may give the idea some thought, if this index really has to be unique. I sometimes use a 'timestamp-insert' column to allow duplicates and retrieve unique entries by a SELECT DISTINCT ON construct. Regards, Christoph ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Stored Procedure Problem
Hi, How to return multiple columns through stored procedure. Consider EX. CREATE FUNCTION b_function() RETURNS varchar AS ' DECLARE an_integer int4; an_name varchar; BEGIN select into an_integer emp_id,an_name emp_name from employee; return an_integer,an_name; END; ' LANGUAGE 'plpgsql'; But This Gives Error(For Multiple column , not for single column) Please Let me know. Thanks Atul…
Re: [SQL] Stored Procedure Problem
Atul wrote: CREATE FUNCTION b_function() RETURNS varchar AS ' DECLARE an_integer int4; an_namevarchar; BEGIN select into an_integer emp_id,an_name emp_name from employee; return an_integer,an_name; END; ' First: select into an_integer,an_name emp_id,emp_name... Second: you can't return 2 variables from plpgsql function. Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Performance Problems
Hi, Thanks, i fix the problem, it was the linux configuration. Ahora podés usar Yahoo! Messenger desde tu celular. Aprendé cómo hacerlo en Yahoo! Móvil: http://ar.mobile.yahoo.com/sms.html ---(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] primary + foreign key
On Thu, Dec 12, 2002 at 10:54:21AM +0100, Huub wrote: > Thanks for pointing out. I'm not that experienced in SQL yet; so detail > could as well be some extra instruction and I've never seen 'references > master' before. But I think I'll manage now. Thanks Read "An Introduction to Database Systems" by C.J. Date. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Wasn't dat special? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Stored Procedure Problem
On Thu, Dec 12, 2002 at 08:13:22PM +0530, Atul wrote:
> Hi,
> How to return multiple columns through stored procedure.
> But This Gives Error(For Multiple column , not for single column)
> Please Let me know.
You didn't say which version of PostgreSQL you are using.
In PG 7.2 you can return a cursor.
In 7.3 you can return a cursor or a true record set.
See http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html
(true to 7.2 and 7.3)
Also
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html
("Returning from a function"). The part on returning record sets is only
relevant to 7.3
-Roberto
--
+|Roberto Mello -http://www.brasileiro.net/ |--+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
:.:..::..:::.:..:..:.:..:...:...:: => BRAILE TAGLINE!
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] function replace doesnt exist
Hi im trying to execute an update command on a postgresql DB table using pgAdmin II im using the following statement UPDATE commandlist SET command = REPLACE (command,'A','B') commandlist is the table name command is the column and i want to change the value A to B but it's giving me the following error message an error has occured in pgAdmin II:frmSQLInput.cmdExecute_Click: Number: -2147467259 Description: Error while executing the query; ERROR: Function'replace(varchar, unknown, unknown)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts ---(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] function replace doesnt exist
If you want character translation like the tr command under unix, use TRANSLATE. Andy Morrow wrote: > > Hi > > im trying to execute an update command on a postgresql DB table using > pgAdmin II > > im using the following statement > > UPDATE commandlist SET command = REPLACE (command,'A','B') > > commandlist is the table name > command is the column > and i want to change the value A to B > > but it's giving me the following error message > > an error has occured in pgAdmin II:frmSQLInput.cmdExecute_Click: > > Number: -2147467259 > Description: Error while executing the query; > ERROR: Function'replace(varchar, unknown, unknown)' does not exist > Unable to identify a function that satisfies the given argument types > You may need to add explicit typecasts > > ---(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] ON DELETE CASCADE
I'm trying to comb through my database and add ON DELETE CASCADE to a
number of tables where I already have fkeys in place, but I'm having a
hard time.
ALTER TABLE project_task DROP CONSTRAINT
"project_task_group_project_id_f" RESTRICT;
ERROR: ALTER TABLE / DROP CONSTRAINT: project_task_group_project_id_f
does not exist
ALTER TABLE project_task
ADD CONSTRAINT projecttask_groupprojectid_fk
FOREIGN KEY (group_project_id)
REFERENCES project_group_list(group_project_id) ON DELETE CASCADE;
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ALTER
That command works, but now I think I have 2x as many triggers as I
want. How do I get rid of the original triggers?
alexandria=# \d project_task
Table "project_task"
Column | Type | Modifiers
--+--+---
project_task_id | integer | not null default
nextval('project_task_pk_seq'::text)
group_project_id | integer | not null default '0'
summary | text | not null default ''
details | text | not null default ''
percent_complete | integer | not null default '0'
priority | integer | not null default '0'
hours| double precision | not null default '0.00'
start_date | integer | not null default '0'
end_date | integer | not null default '0'
created_by | integer | not null default '0'
status_id| integer | not null default '0'
Indexes: projecttask_projid_status
Primary key: project_task_pkey
Triggers: RI_ConstraintTrigger_51030049,
RI_ConstraintTrigger_51030047,
RI_ConstraintTrigger_4305858,
RI_ConstraintTrigger_4305852,
RI_ConstraintTrigger_4305846
After adding the new foreign key:
Triggers: RI_ConstraintTrigger_51364957, ***new
RI_ConstraintTrigger_51030049,
RI_ConstraintTrigger_51030047,
RI_ConstraintTrigger_4305858,
RI_ConstraintTrigger_4305852,
RI_ConstraintTrigger_4305846
---(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] ON DELETE CASCADE
On Thu, 12 Dec 2002, Tim Perdue wrote: > I'm trying to comb through my database and add ON DELETE CASCADE to a > number of tables where I already have fkeys in place, but I'm having a > hard time. > > ALTER TABLE project_task DROP CONSTRAINT > "project_task_group_project_id_f" RESTRICT; > > ERROR: ALTER TABLE / DROP CONSTRAINT: project_task_group_project_id_f > does not exist I think this works in 7.3, but that's the first version in which it does. > ALTER TABLE project_task > ADD CONSTRAINT projecttask_groupprojectid_fk > FOREIGN KEY (group_project_id) > REFERENCES project_group_list(group_project_id) ON DELETE CASCADE; > NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY > check(s) > ALTER > > That command works, but now I think I have 2x as many triggers as I > want. How do I get rid of the original triggers? You'll need to look at pg_trigger and find the triggers associated with the constraints. I'd suggest removing both sets and then using alter table again because then you can look for triggers that have the correct tables and columns listed in tgargs. As a note there are two triggers on project_group_list for each constraint as well and you'll need to remove those too. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] ON DELETE CASCADE
Here is a function that I use to list fk(triggers) on a table. Execute the
function to get the trigger name and then - drop trigger "trigger_name" on
table_name;
hth
-- Version 1.0, June 2002
-- Marie G. Tuite
-- Function lists FK by table.
-- To execute:
-- begin;select fn_list_fk('table_name');
-- fetch all from ""; end;
create or replace function fn_list_fk(name)
returns refcursor as
'
declare
table_in alias for $1;
rc refcursor;
begin
open rc for
select tgname as trigger_name_sys
,tgconstrname as trigger_name_given
,b.relname as table_name
,tgisconstraint as ri
,c.relname as parent_table
from pg_trigger a,
pg_class b,
pg_class c
where a.tgrelid=b.oid and b.relname=table_in
and tgconstrrelid = c.oid;
return rc;
end;
'
language 'plpgsql'
;
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Tim Perdue
> Sent: Thursday, December 12, 2002 11:01 AM
> To: [EMAIL PROTECTED]
> Subject: [SQL] ON DELETE CASCADE
>
>
> I'm trying to comb through my database and add ON DELETE CASCADE to a
> number of tables where I already have fkeys in place, but I'm having a
> hard time.
>
> ALTER TABLE project_task DROP CONSTRAINT
> "project_task_group_project_id_f" RESTRICT;
>
> ERROR: ALTER TABLE / DROP CONSTRAINT: project_task_group_project_id_f
> does not exist
>
> ALTER TABLE project_task
> ADD CONSTRAINT projecttask_groupprojectid_fk
> FOREIGN KEY (group_project_id)
> REFERENCES project_group_list(group_project_id) ON DELETE CASCADE;
> NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ALTER
>
> That command works, but now I think I have 2x as many triggers as I
> want. How do I get rid of the original triggers?
>
>
> alexandria=# \d project_task
> Table "project_task"
>Column | Type | Modifiers
> --+--+---
> project_task_id | integer | not null default
> nextval('project_task_pk_seq'::text)
> group_project_id | integer | not null default '0'
> summary | text | not null default ''
> details | text | not null default ''
> percent_complete | integer | not null default '0'
> priority | integer | not null default '0'
> hours| double precision | not null default '0.00'
> start_date | integer | not null default '0'
> end_date | integer | not null default '0'
> created_by | integer | not null default '0'
> status_id| integer | not null default '0'
> Indexes: projecttask_projid_status
> Primary key: project_task_pkey
> Triggers: RI_ConstraintTrigger_51030049,
>RI_ConstraintTrigger_51030047,
>RI_ConstraintTrigger_4305858,
>RI_ConstraintTrigger_4305852,
>RI_ConstraintTrigger_4305846
>
> After adding the new foreign key:
>
> Triggers: RI_ConstraintTrigger_51364957, ***new
>RI_ConstraintTrigger_51030049,
>RI_ConstraintTrigger_51030047,
>RI_ConstraintTrigger_4305858,
>RI_ConstraintTrigger_4305852,
>RI_ConstraintTrigger_4305846
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ON DELETE CASCADE
On 12 Dec 2002 at 11:01, Tim Perdue wrote: > That command works, but now I think I have 2x as many triggers as I > want. How do I get rid of the original triggers? I had to do something similar and documented it at http://www.freebsddiary.org/postgresql-dropping-constraints.php hth -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] primary + foreign key
On Thu, 2002-12-12 at 10:27, Roberto Mello wrote: > > Read "An Introduction to Database Systems" by C.J. Date. > Boy that brings back memories. I think I will dig it out of a box since I am doing database coding again after a long hiatus. Seems like I had a couple of Date books on my shelf at on time. After lurking several pgsql lists for a while now I see why the db2 dbas used to get so bent out of shape with boneheaded queries. Especially since each project got billed for MVS CPU time :) Bret ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] function replace doesnt exist
What version are you using? I think "replace" is new for version 7.3. As another poster has pointed out, "translate" works for individual characters. Or you could code up your own replace using the native functionality of pl/perl or pl/tcl or such. Or you could just upgrade to 7.3. --- Andy Morrow <[EMAIL PROTECTED]> wrote: > Hi > > im trying to execute an update command on a > postgresql DB table using > pgAdmin II > > im using the following statement > > UPDATE commandlist SET command = REPLACE > (command,'A','B') > > > commandlist is the table name > command is the column > and i want to change the value A to B > > > but it's giving me the following error message > > > an error has occured in pgAdmin > II:frmSQLInput.cmdExecute_Click: > > Number: -2147467259 > Description: Error while executing the query; > ERROR: Function'replace(varchar, unknown, unknown)' > does not exist > Unable to identify a function that satisfies the > given argument types > You may need to add explicit typecasts > > > ---(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 __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(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] client for solaris
Hi all, I am looking for a postgreSQL client for installing on a Solaris 8 machine. The basic idea is to use the client and retrive the data from a database running on another machine without actually connecting to the database server. So, I want to know where I can get the client for solaris? I am assuming postgreSQL will run on solaris? I can make a package from the source. By client, I mean the client libraries. thanks, Aravind ---(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
