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(at)postgresql(dot)org)
You don't need the VALUES when you select ie:
INSERT INTO tbl (a,b) SELECT a,b FROM tbl2;
hth,
- Stuart