I have a web site devoted to quest guides for World of Warcraft
players. There is a view that gets all the data needed for the quest
details page from several tables. The view currently looks like this
(joins indicating non-null columns and left joins indicating nullable
columns):
SELECT q.id, q.name, q.series, r.name AS series_name, q.summary,
q.instructions, q.experience, q.notes, st.id AS stage, st.description
AS warning, s.id AS sid, s.name AS starts, s.description AS sdesc,
e.id AS eid, e.name AS ends, e.description AS edesc,
COALESCE('/zones/'::text || q."zone"::text, '/instances/'::text ||
q.instance::text) AS "location", COALESCE(z.name, i.name) AS
location_name, sz.name AS szone, ez.name AS ezone, si.name AS
sinstance, ei.name AS einstance
FROM quests q
JOIN stages st ON st.id = q.stage
JOIN npcs s ON s.id = q.starts
JOIN npcs e ON e.id = q.ends
LEFT JOIN series r ON r.id = q.series
LEFT JOIN zones z ON z.id = q."zone"
LEFT JOIN instances i ON i.id = q.instance
LEFT JOIN zones sz ON sz.id = s."zone"
LEFT JOIN zones ez ON ez.id = e."zone"
LEFT JOIN instances si ON si.id = s.instance
LEFT JOIN instances ei ON ei.id = e.instance;
Now it turns out that this view is based on an old analysis of the
problem domain which is proving unrealistic.
For now, we'll stick to just one change: I imagined that each quest
would have only one "quest-giver", so I could use a foreign key column
("starts") in the quests table pointing to the npcs table.
It turns out that a few quests can be given by several npcs (and npcs
in general will give multiple quests) so I want to move to a link
table approach.
Now a link table means that the query will return several records, one
for each quest giver, where the quest data will reman the same and the
npc data will differ. I've seen and maintained old VB code that takes
this approach, filling the master form fields using just the first
record and the detail form fields using all the records, and while it
works it's never impressed me as being particularly elegant. And of
course the problem will only get compounded when I have to add
multiple quest-enders too.
My proposed solution is to replace the view with a function returning
a single record. The quest starter fields sid, starts, sdesc, szone
and sinstance will become arrays, and I'll fill them in using a
separate query.
So I settled on a plan of action, with testing at each step:
1) Convert the current view to a function returning a record, without
changing anything.
2) Separate out the population of the quest starter and quest ender
details into new queries. The queries still return only one record,
based on the old fk fields.
3) Change the field types of the quest starter and quest ender fields
to arrays. Keep the quests that select into them the same as before,
except that they now select into the 0th element of the arrays.
4) Change the quests that select into the starter and ender details
fields so that they return multiple records where appropriate. It
would be lovely if postgres automatically noticed that there were
several rows in the results and the targets were arrays, and just took
care of it, but more likely loop through the rows and stuff the values
into the arrays manually.
5) Champagne!
I got as far as step 2, and that works perfectly, but I'm getting a
mysterious error at step 3. The function and its associated data type
look like this at step 3:
CREATE TYPE foo_type AS
(
id INTEGER
, name VARCHAR(60)
, summary TEXT
, instructions TEXT
, experience TEXT
, notes TEXT
, starts INTEGER[]
, ends INTEGER
, stage INTEGER
, warning VARCHAR(100)
, sname VARCHAR(40)[]
, sdesc TEXT[]
, slocation VARCHAR(40)[]
, ename VARCHAR(40)
, edesc TEXT
, elocation VARCHAR(40)
, series INTEGER
, series_name VARCHAR(40)
, location TEXT
, location_name TEXT
)
;
DECLARE
results foo_type;
BEGIN
SELECT
q.id, q.name, q.summary, q.instructions, q.experience, q.notes,
q.starts, q.ends
, q.stage, st.description
, q.series, r.name
, COALESCE('../zones/' || q.zone, '../instances/' || q.instance)
, COALESCE(z.name, i.name)
INTO
results.id
, results.name
, results.summary
, results.instructions
, results.experience
, results.notes
, results.starts[0]
, results.ends
, results.stage
, results.warning
, results.series
, results.series_name
, results.location
, results.location_name
FROM
quests q
JOIN stages st ON st.id = q.stage
JOIN npcs e ON e.id = q.ends
LEFT JOIN series r ON r.id = q.series
LEFT JOIN zones z ON z.id = q.zone
LEFT JOIN instances i ON i.id = q.instance
WHERE
q.id = $1
;
SELECT
n.name
, n.description
, COALESCE(z.name, i.name)
INTO
results.sname[0]
, results.sdesc[0]
, results.slocation[0]
FROM
npcs n
LEFT JOIN zones z ON z.id = n.zone
LEFT JOIN instances i ON i.id = n.instance
WHERE
n.id = results.starts
;
SELECT
n.name
, n.description
, COALESCE(z.name, i.name)
INTO
results.ename
, results.edesc
, results.elocation
FROM
npcs n
LEFT JOIN zones z ON z.id = n.zone
LEFT JOIN instances i ON i.id = n.instance
WHERE
n.id = results.ends
;
RETURN results;
end
The error says:
ggw_dev=> select * from testfunc(3);
ERROR: syntax error at or near "[" at character 226
QUERY: SELECT q.id, q.name, q.summary, q.instructions, q.experience,
q.notes, q.starts, q.ends , q.stage, st.description , q.series, r.name
, COALESCE('../zones/' || q.zone, '../instances/' || q.instance) ,
COALESCE(z.name, i.name)[0] , $1 , $2 , $3 , $4 , $5 , $6
, $7 FROM quests q JOIN stages st ON st.id = q.stage JOIN npcs e ON
e.id = q.ends LEFT JOIN series r ON r.id = q.series LEFT JOIN zones z
ON z.id = q.zone LEFT JOIN instances i ON i.id = q.instance WHERE q.id
= $8
CONTEXT: PL/pgSQL function "testfunc" line 6 at select into variables
LINE 1: ...tances/' || q.instance) , COALESCE(z.name, i.name)[0] , $1 ...
^
Where the caret is pointing to the left square bracket after the
closing round bracket of coalesce. The odd thing is of course that in
the source of the function there is no "[0]" in that location at all.
What I want to know is, is what I am trying to do even possible (my
ISP is running pg 8.0.8)? If it is, have I got the syntax right? Or
perhaps step 3 isn't possible but step 4 is? Oh, and if all that is
doable, do I get my wish in step 4 or do I do it manually?
Regards,
Gerard.
