Обсуждение: Converting a proceedure from SOLID to Postgres

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

"Bob Whitehouse" <bwhitehouse@geeknest.com> writes:
> 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()

plpgsql believes (for no good reason AFAICS) that a SELECT that doesn't
put its results someplace must be a mistake.  Therefore it wants you
to do SELECT INTO rather than plain SELECT.  If you're only doing the
SELECT so that you can check FOUND or ROW_COUNT, you still need to
select into a dummy variable.

As near as I can tell, the function you are trying to translate also
does a SELECT INTO and returns the result of that select (if
successful).  So in reality, your translation is wrong anyway.
I think you want something like

    declare
        person int4;
    begin

          SELECT h.who INTO person
          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 h.id DESC LIMIT 1;

          IF NOT FOUND THEN
                 person := 0;
          END IF;

          RETURN person;

but I'm just guessing...

            regards, tom lane