Is the following expected behavior for a UNION query with ORDER BY:
executing this query:
+++++++++++++++++++++++++++++++++++++++++++
SELECT a.attnum as number,
a.attname as attribute,
CASE WHEN t.typname = 'varchar' THEN
t.typname || '(' || a.atttypmod - 4 || ')'
ELSE
t.typname
END as type,
CASE WHEN a.attnotnull = 't' THEN
'not null '::text ELSE ''::text
END || 'default ' ||
CASE WHEN a.atthasdef = 't' THEN
substring(d.adsrc for 128)::text
ELSE ''::text END as modifier
FROM pg_class c,
pg_attribute a,
pg_type t,
pg_attrdef d
WHERE c.relname = 'tblplayer' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
c.oid = d.adrelid AND
d.adnum = a.attnum
UNION ALL
SELECT a.attnum as number,
a.attname as attribute,
CASE WHEN t.typname = 'varchar' THEN
t.typname || '(' || a.atttypmod - 4 || ')'
ELSE
t.typname
END as type,
CASE WHEN a.attnotnull = 't' THEN
'not null '::text
ELSE
''::text
END as modifier
FROM pg_class c,
pg_attribute a,
pg_type t
WHERE c.relname = 'tblplayer' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
a.attname NOT IN (SELECT a.attname
FROM pg_class c,
pg_attribute a,
pg_attrdef d
WHERE c.relname = 'tblplayer' AND
a.attnum > 0 AND
a.attrelid = c.oid AND
a.atttypid = t.oid AND
c.oid = d.adrelid AND
d.adnum = a.attnum)
ORDER BY a.attnum;
+++++++++++++++++++++++++++++++++++++++
yields
number | attribute | type | modifier
--------+---------------+-------------+--------------------------------
1 | play_id | int4 | not null default nextval('...
2 | play_name | varchar(30) | not null
3 | play_username | varchar(16) | not null
4 | play_password | varchar(16) | not null
5 | play_online | bool | default 'f'
However, if I execute the same query and drop "a.attnum as number" from
the select part, it returns the following:
attribute | type | modifier
---------------+-------------+--------------------------------
play_id | int4 | not null default nextval('...
play_online | bool | default 'f'
play_name | varchar(30) | not null
play_username | varchar(16) | not null
play_password | varchar(16) | not null
which is incorrect accoring to the initial query. It appears to be
ordering the individual selects and then appending the second query to
the first -- is this correct?
Thanks
Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio
