Converting a proceedure from SOLID to Postgres
От | Bob Whitehouse |
---|---|
Тема | Converting a proceedure from SOLID to Postgres |
Дата | |
Msg-id | 00f601c0d4de$a27b6d40$a6a0fea9@amsite.com обсуждение исходный текст |
Ответы |
Re: Converting a proceedure from SOLID to Postgres
|
Список | 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
В списке pgsql-novice по дате отправления: