Обсуждение: PERFORM statement question

Поиск
Список
Период
Сортировка

PERFORM statement question

От
"Keith Worthington"
Дата:
Hi All,

Well, I am continueing to make progress on my function to transfer or update
data as required.  However I have hit another snag.  After I successfully
create the function below I atttempt to run it with the command

SELECT xfer_gl_account_data();

And get the error

WARNING:  Error occurred while executing PL/pgSQL function xfer_gl_account_data
WARNING:  line 11 at assignment
ERROR:  parser: parse error at or near "SELECT" at character 9

This was an error regarding not using a PERFORM statement but now the PERFORM
is in there and it still won't work.  I have read
http://www.postgresql.org/docs/7.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-PERFORM
to no avail.

Obviously I am not using the PERFORM command peroperly but I do not
understand.  Hints greatly appreciated.

Kind Regards,
Keith

CREATE OR REPLACE FUNCTION xfer_gl_account_data() RETURNS INTEGER AS '
   DECLARE
      rcrd_gl_account RECORD;
   BEGIN
      FOR rcrd_gl_account IN SELECT
data_transfer.tbl_peachtree_gl_account.account_id,

data_transfer.tbl_peachtree_gl_account.description,

data_transfer.tbl_peachtree_gl_account.account_type,

data_transfer.tbl_peachtree_gl_account.inactive
                               FROM data_transfer.tbl_peachtree_gl_account
                              ORDER BY
data_transfer.tbl_peachtree_gl_account.account_id
      LOOP
         PERFORM SELECT peachtree.tbl_gl_account.account_id
                   FROM peachtree.tbl_gl_account
                  WHERE peachtree.tbl_gl_account.account_id =
rcrd_gl_account.account_id;
         IF NOT FOUND THEN
            INSERT INTO peachtree.tbl_gl_account
                        ( peachtree.tbl_gl_account.account_id,
                          peachtree.tbl_gl_account.description,
                          peachtree.tbl_gl_account.account_type,
                          peachtree.tbl_gl_account.inactive )
                 VALUES ( rcrd_gl_account.account_id,
                          rcrd_gl_account.description,
                          rcrd_gl_account.account_type,
                          rcrd_gl_account.inactive );
         ELSE
            UPDATE peachtree.tbl_gl_account
               SET peachtree.tbl_gl_account.description =
rcrd_gl_account.description,
                   peachtree.tbl_gl_account.account_type =
rcrd_gl_account.account_type,
                   peachtree.tbl_gl_account.inactive = rcrd_gl_account.inactive
             WHERE peachtree.tbl_gl_account.account_id =
rcrd_gl_account.account_id;
         END IF;
      END LOOP;
      RETURN 1;
   END;
' LANGUAGE 'plpgsql';

______________________________________________
99main Internet Services http://www.99main.com

Re: PERFORM statement question

От
Tom Lane
Дата:
"Keith Worthington" <keithw@narrowpathinc.com> writes:
>          PERFORM SELECT peachtree.tbl_gl_account.account_id
>                    FROM peachtree.tbl_gl_account
>                   WHERE peachtree.tbl_gl_account.account_id =
> rcrd_gl_account.account_id;

You just want "PERFORM peachtree...", that is, the PERFORM keyword
is syntactically a substitute for SELECT.  The manual is not very
good about this :-( ... I've tried to make it clearer in the 8.0
docs.

            regards, tom lane

Re: PERFORM statement question

От
"Keith Worthington"
Дата:
Replying to myself:

Woohoo!

On a whim I changed PERFORM SELECT... to PERFORM and... something else broke.
 As I am sure the experienced programmers are saying "Well of course you items
are over specified."  Yeah, yeah.  We all have to learn. :-)  Fixed that and
wham, it worked.

Now I need to add a couple more conditionals and I will almost be there.

Keith

Hi All,

Well, I am continueing to make progress on my function to transfer or update
data as required.  However I have hit another snag.  After I successfully
create the function below I atttempt to run it with the command

SELECT xfer_gl_account_data();

And get the error

WARNING:  Error occurred while executing PL/pgSQL function xfer_gl_account_data
WARNING:  line 11 at assignment
ERROR:  parser: parse error at or near "SELECT" at character 9

This was an error regarding not using a PERFORM statement but now the PERFORM
is in there and it still won't work.  I have read
http://www.postgresql.org/docs/7.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-PERFORM
to no avail.

Obviously I am not using the PERFORM command peroperly but I do not
understand.  Hints greatly appreciated.

Kind Regards,
Keith

CREATE OR REPLACE FUNCTION xfer_gl_account_data() RETURNS INTEGER AS '
   DECLARE
      rcrd_gl_account RECORD;
   BEGIN
      FOR rcrd_gl_account IN SELECT
data_transfer.tbl_peachtree_gl_account.account_id,

data_transfer.tbl_peachtree_gl_account.description,

data_transfer.tbl_peachtree_gl_account.account_type,

data_transfer.tbl_peachtree_gl_account.inactive
                               FROM data_transfer.tbl_peachtree_gl_account
                              ORDER BY
data_transfer.tbl_peachtree_gl_account.account_id
      LOOP
         PERFORM SELECT peachtree.tbl_gl_account.account_id
                   FROM peachtree.tbl_gl_account
                  WHERE peachtree.tbl_gl_account.account_id =
rcrd_gl_account.account_id;
         IF NOT FOUND THEN
            INSERT INTO peachtree.tbl_gl_account
                        ( peachtree.tbl_gl_account.account_id,
                          peachtree.tbl_gl_account.description,
                          peachtree.tbl_gl_account.account_type,
                          peachtree.tbl_gl_account.inactive )
                 VALUES ( rcrd_gl_account.account_id,
                          rcrd_gl_account.description,
                          rcrd_gl_account.account_type,
                          rcrd_gl_account.inactive );
         ELSE
            UPDATE peachtree.tbl_gl_account
               SET peachtree.tbl_gl_account.description =
rcrd_gl_account.description,
                   peachtree.tbl_gl_account.account_type =
rcrd_gl_account.account_type,
                   peachtree.tbl_gl_account.inactive = rcrd_gl_account.inactive
             WHERE peachtree.tbl_gl_account.account_id =
rcrd_gl_account.account_id;
         END IF;
      END LOOP;
      RETURN 1;
   END;
' LANGUAGE 'plpgsql';

______________________________________________
99main Internet Services http://www.99main.com