[SQL] Return Primary Key from Procedure

[Peter Atkins]

All,

I have two tables t_proj, t_task see below:

CREATE TABLE t_proj (
proj_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (proj_id),
task_id integer(12),
user_id integer(6),
title varchar(35),
description varchar(80)
);

CREATE TABLE t_task (
task_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (task_id),
title varchar(35),
description varchar(80)
);

When I insert into t_task I need to return the task_id (PK) for that insert
to be used for the insert into the t_proj table.

I tried using RESULT_OID but I have no idea how to obtain the true PK using
this opague id. Below is the procedure I tried to use.

CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
RETURNS INTEGER AS '

DECLARE
-- local variables
oid1 INTEGER;
retval INTEGER;

BEGIN
INSERT INTO t_task (title, description) VALUES ($1, $2);

-- Get the oid of the row just inserted.
GET DIAGNOSTICS oid1 = RESULT_OID;

retval := oid1;

-- Everything has passed, return id as pk
RETURN retval;
END;
' LANGUAGE 'plpgsql';


Any help would be great! 

Thanks Again,
-p

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] Returning PK of first insert for second insert use.

[Peter Atkins]

All,

I have two tables t_proj, t_task see below:

CREATE TABLE t_proj (
proj_id SERIAL NOT NULL,
PRIMARY KEY (proj_id),
task_id integer(12),
user_id integer(6),
title varchar(35),
description varchar(80)
);

CREATE TABLE t_task (
task_id SERIAL NOT NULL,
PRIMARY KEY (task_id),
title varchar(35),
description varchar(80)
);

When I insert into t_task I need to return the task_id (PK) for that insert
to be used for the insert into the t_proj table.

I tried using RESULT_OID but I have no idea how to obtain the true PK using
this opague id. Below is the procedure I tried to use.

CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
RETURNS INTEGER AS '

DECLARE
-- local variables
oid1 INTEGER;
retval INTEGER;

BEGIN
INSERT INTO t_task (title, description) VALUES ($1, $2);

-- Get the oid of the row just inserted.
GET DIAGNOSTICS oid1 = RESULT_OID;

retval := oid1;

-- Everything has passed, return id as pk
RETURN retval;
END;
' LANGUAGE 'plpgsql';


Any help would be great! 

Thanks Again,
-p



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Returning PK of first insert for second insert use.

[Peter Atkins]

Thank you for explaining that in detail it makes sense now. I'll give it a
try.
Thanks again!
-p

-Original Message-
From: Ken Corey [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 29, 2002 1:05 PM
To: Peter Atkins
Cc: '[EMAIL PROTECTED]'
Subject: RE: Returning PK of first insert for second insert use.


On Mon, 2002-07-29 at 20:52, Peter Atkins wrote:
> Is there a possibility of another application accessing the DB and using
the
> id before my function has completed the transaction? I'm concerned with
the
> possibility of cross-over of ID's if the insert hangs.
> 
> There's no way to return the id of that insert inherently, and then use it
> for the second insert? I think SQL uses something like ADD_ID, not sure.

That's the beauty of the nextval statement. The database internally
sequences requests to it so that you're kept out of harm's way.

Say process A called the function,and nextval returns 16.  The function
now continues on its way, but is not finished when process B then calls
the function (before A is done), and nextval returns 17.

So, then function called by process A returns 16, and the function
called by process B returns 17.

That means that unless the results of process B depend in some way upon
the results of process A, there's no problem.

-Ken

-- 
Ken Corey  CTO  http://www.atomic-interactive.com  07720 440 731

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Returning PK of first insert for second insert use.

[Peter Atkins]

All,

I have two tables t_proj, t_task see below:

CREATE TABLE t_proj (
proj_id SERIAL NOT NULL,
PRIMARY KEY (proj_id),
task_id integer(12),
user_id integer(6),
title varchar(35),
description varchar(80)
);

CREATE TABLE t_task (
task_id SERIAL NOT NULL,
PRIMARY KEY (task_id),
title varchar(35),
description varchar(80)
);

When I insert into t_task I need to return the task_id (PK) for that insert
to be used for the insert into the t_proj table.

I tried using RESULT_OID but I have no idea how to obtain the true PK using
this opague id. Below is the procedure I tried to use.

CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
RETURNS INTEGER AS '

DECLARE
-- local variables
oid1 INTEGER;
retval INTEGER;

BEGIN
INSERT INTO t_task (title, description) VALUES ($1, $2);

-- Get the oid of the row just inserted.
GET DIAGNOSTICS oid1 = RESULT_OID;

retval := oid1;

-- Everything has passed, return id as pk
RETURN retval;
END;
' LANGUAGE 'plpgsql';


Any help would be great! 

Thanks Again,
-p

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Delete function without knowing the elements

[Peter Atkins]

All,

I created a function that I can't seem to delete.

[no sure how many parameters]

CREATE OR REPLACE FUNCTION insertEntry (int4, varchar, varchar, numeric,
varchar, timestamp, varchar, int4, numeric, varchar, int4, ,)
RETURNS INT4 AS '
.
END;
' LANGUAGE 'plpgsql';

How can I delete this without knowing how many I used to create it? This is
what I used to delete others in the past.

DROP FUNCTION insertEntry(int4, varchar, varchar, ...);

Thanks,
-pete


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Changing Column Type

[Peter Atkins]

All,

Is there a way to easily change the type of  column? Or do I have to drop
and create again.

From:
assignment_notes | character varying(255)

To:
assignment_notes | text

Thanks,
-p


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Passing array to PL/SQL and looping

[Peter Atkins]

All,

I'm a newbie to PL/SQL and need help badly. I'm trying to pass my array of
id's to the function and then loop through until the array is empty. I know
there must be atleast five things I'm doing wrong. 

Please help!

Cheers,
-p


Call to Procedure and Array:

$myArray = array(15, 6, 23);
select generateInvoice($myArray);

Procedure:

CREATE FUNCTION generateInvoice (VARRAY) RETURNS int4 AS '

DECLARE
-- local variables
temppk INT4;
v_pids := $1;
v_count BINARY_INTEGER := 1;
id INT4;

BEGIN
SELECT INTO temppk nextval(''t_task_task_id_seq'');

LOOP
IF v_pids.EXISTS(v_count) THEN

id := v_pids.NEXT(v_count);

UPDATE t_project SET task_id=temppk WHERE project_id=id;

v_count := v_count + 1;

ELSE
EXIT;
END IF;
END LOOP;

-- Everything has passed, return id as pk
RETURN temppk;
END;
' LANGUAGE 'plpgsql';

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]