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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: Knut Suebert
Дата:
Сообщение: Re: unique (a,b)?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Converting a proceedure from SOLID to Postgres