Thread: Error when calling this function....

Am I wrong to assume that I can simply call the following function
with SELECT catalog_batch(); ?
Additionally, I am still unclear as to how I should handle the return
type for this function.  Is what I have ok?  Should the word Complete
in the return statement be quoted?


CREATE FUNCTION catalog_batch() RETURNS text AS '

    DECLARE
        mp3rec RECORD;
    BEGIN
        FOR mp3rec IN SELECT * FROM mp3catalog LOOP

        INSERT INTO album (title, media, path, release_date)
VALUES (mp3catalog.album, mp3catalog.audioformat, mp3catalog.path,
mp3catalog.year);

        INSERT INTO track (album_id, trk_no, trk_title, time,
genre, bitrate, channel, notes) VALUES (SELECT
currval("album_album_id_seq"), mp3catalog.track, mp3catalog.title,
mp3catalog.time, mp3catalog.genre, mp3catalog.bitrate,
mp3catalog.channels, mp3catalog.comment);

        INSERT INTO participant (name) VALUES (mp3catalog.artist);

        INSERT INTO performance (album_id, participant_id)
VALUES (SELECT currval("album_album_id_seq"), SELECT
currval("participant_participant_id_seq"));

        END LOOP;
        RETURN Complete;
    END;'

    LANGUAGE 'plpgsql';

recordings=> select catalog_batch();
NOTICE:  Error occurred while executing PL/pgSQL function catalog_batch
NOTICE:  line 9 at SQL statement
ERROR:  parser: parse error at or near "SELECT"

and from phpPgAdmin:

Database recordings

No table detected... unable to retrieve primary or unique keys for edit/delete
Error - /Library/WebServer/Documents/phpPgAdmin/sql.php -- Line: 112

PostgreSQL said: ERROR: parser: parse error at or near "SELECT"
Your query:
select catalog_batch()

Dan Jewett wrote:
> Am I wrong to assume that I can simply call the following function
> with SELECT catalog_batch(); ?
> Additionally, I am still unclear as to how I should handle the return
> type for this function.  Is what I have ok?  Should the word Complete
> in the return statement be quoted?
>
>
> CREATE FUNCTION catalog_batch() RETURNS text AS '
>
>       DECLARE
>               mp3rec RECORD;
>       BEGIN
>               FOR mp3rec IN SELECT * FROM mp3catalog LOOP
>
>               INSERT INTO album (title, media, path, release_date)
> VALUES (mp3catalog.album, mp3catalog.audioformat, mp3catalog.path,
> mp3catalog.year);
>
>               INSERT INTO track (album_id, trk_no, trk_title, time,
> genre, bitrate, channel, notes) VALUES (SELECT
> currval("album_album_id_seq"), mp3catalog.track, mp3catalog.title,
> mp3catalog.time, mp3catalog.genre, mp3catalog.bitrate,
> mp3catalog.channels, mp3catalog.comment);
>
>               INSERT INTO participant (name) VALUES
> (mp3catalog.artist);
>
>               INSERT INTO performance (album_id, participant_id)
> VALUES (SELECT currval("album_album_id_seq"), SELECT
> currval("participant_participant_id_seq"));
>
>               END LOOP;
>               RETURN Complete;
>       END;'
>
>       LANGUAGE 'plpgsql';
>
> recordings=> select catalog_batch();
> NOTICE:  Error occurred while executing PL/pgSQL function
> catalog_batch NOTICE:  line 9 at SQL statement
> ERROR:  parser: parse error at or near "SELECT"
>
> and from phpPgAdmin:
>
> Database recordings
>
> No table detected... unable to retrieve primary or unique keys for
> edit/delete Error - /Library/WebServer/Documents/phpPgAdmin/sql.php
> -- Line: 112
>
> PostgreSQL said: ERROR: parser: parse error at or near "SELECT" Your
> query: select catalog_batch()
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
You don't need the VALUES when you select ie:
INSERT INTO tbl (a,b) SELECT a,b FROM tbl2;
hth,
- Stuart

You don't need the VALUES when you select ie:
INSERT INTO tbl (a,b) SELECT a,b FROM tbl2;
hth,

- Stuart