Converting a proceedure from SOLID to Postgres

From: "Bob Whitehouse" <bwhitehouse(at)geeknest(dot)com>
To: "pgsql-novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Converting a proceedure from SOLID to Postgres
Date: 2001-05-04 21:10:23
Message-ID: 00f601c0d4de$a27b6d40$a6a0fea9@amsite.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I'm trying to move a database out of SOLID and into Postgres. Durning the
course of this process I've been translating all of the stored
procedures into the PL/Pgsql language. I've had success with everything
except this one function.

Here is the SOLID version:

"CREATE PROCEDURE GET_LAST_RESPONDENT(ISSUE_ID_VAR INTEGER)
RETURNS (PERSON INTEGER)
BEGIN
EXEC SQL WHENEVER SQLERROR ABORT;
EXEC SQL PREPARE C1 SELECT H.WHO, ISS.ID AS ISSUE, H.ID AS HISTID
FROM HISTORY H, ISSUES ISS
WHERE ISS.ID = ?
AND ISS.ID = H.ISSUE
AND H.H_TYPE = 3
AND H.WHO <> ISS.SUBMITTER
ORDER BY HISTID DESC;
EXEC SQL EXECUTE C1 USING (ISSUE_ID_VAR) INTO (PERSON);
EXEC SQL FETCH C1;
IF NOT SQLSUCCESS THEN
PERSON := 0;
END IF
EXEC SQL CLOSE C1;
EXEC SQL DROP C1;
END
";

Here is where I am with Postgres version:

CREATE FUNCTION get_last_respondent(INT4)
RETURNS INT4
AS 'DECLARE
int_issue_id_var ALIAS FOR $1;
int_succ INT4;

BEGIN
SELECT h.who, iss.id AS issue, h.id AS histid
FROM history h, issues iss
WHERE iss.id = int_issue_id_var
AND iss.id = h.issue
AND h.h_type = 3
AND h.who <> iss.submitter
ORDER BY histid DESC;

IF NOT FOUND THEN
int_succ := 0;
ELSE
GET DIAGNOSICS int_succ = ROW_COUNT;
END IF;
RETURN int_succ;
END;'
LANGUAGE 'plpgsql';

When I run this I get this error message:

SQL: select get_last_respondent(1290)
[Fri May 4 16:30:40 2001] null: DBD::Pg::st execute failed: ERROR:
unexpected SELECT query in exec_stmt_execsql()

I want the function to return the number of records retururned by the query
and make the records available to the application. I've tried many different
versions of this and am pretty frustrated with it right now because I know
it has to be a common thing to do. Sorry if this seems dense but I'm pretty
new to it. Please let me know where I'm going wrong.

Thanks, Bob

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message jason.servetar 2001-05-04 22:05:58 default value not working?
Previous Message Knut Suebert 2001-05-04 21:01:59 Re: unique (a,b)?