Converting a proceedure from SOLID to Postgres

Converting a proceedure from SOLID to Postgres

Post by Bob Whitehous » Sun, 06 May 2001 07:19:13



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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

Converting a proceedure from SOLID to Postgres

Post by Tom La » Sun, 06 May 2001 09:02:40



> 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

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

 
 
 

1. Slightly OT - Convert column in Postgres

I am using Perl DBI to do this conversion, so this is somewhat
related to DBI.

I need to convert a column in a Postgres table from VARCHAR to
INTEGER - I don't know of an easy way to do that, so I'm assuming
I need to do something drastic like this in my conversion script:

  * create a new table - with all existing columns except VARCHAR
    column I don't want, and with new INTEGER column

  * populate rows with rows from old table, and populate
    new INTEGER column properly

  * once new table populated, delete old table

  * rename new table to old table name

A couple of hitches - the table has a primary key and a sequence,
but there are no other tables which use that primary key.

Is there an easier way?  What am I missing?

TIA.

Hardy Merrill

2. Lost all my incoming and outgoing e-mail of past 3 years

3. scripts for converting postgres to oracle?

4. Jasmine SCSI experience

5. converting an oracle procedure to postgres

6. Is there a Pasteboard Exterminator?

7. Postgres installataion in SUSE 7.0, lacking dirs i ~postgres/data

8. Text Background went Black

9. Importing Postgres 7.1.1 DBs into Postgres 7.3.2

10. Simple overclocking proceedure wanted

11. SPAWN proceedure under Windows NT

12. CNLR proceedure and Bootstrapping