109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
...
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
|
edited bigint,
parent bigint,
convoheaduri text,
chgcount integer,
-- ephemeral
accent smallint,
rtdby bigint, -- note that these must be 0 if the record
rtid bigint, -- in question does not represent an RT!
n_likes integer,
n_rts integer,
isreply bool -- true if parent in (table posts); saves us a bunch of queries
);
create or replace function
pg_temp.parsavpg_translate_post(parsav_posts,bigint,bigint)
returns pg_temp.parsavpg_intern_post as $$
select a.origin is null,
($1).id, ($1).author,
($1).subject,($1).acl, ($1).body,
($1).posted, ($1).discovered, ($1).edited,
($1).parent, ($1).convoheaduri,($1).chgcount,
coalesce(c.value, -1)::smallint,
$2 as rtdby, $3 as rtid,
re.likes, re.rts,
($1).parent in (select id from parsav_posts)
from parsav_actors as a
left join parsav_actor_conf_ints as c
on c.key = 'ui-accent' and
c.uid = a.id
left join pg_temp.parsavpg_post_react_counts as re
................................................................................
inner join parsav_posts as p on a.subject = p.id
where a.kind = 'rt'
),
content as (select * from posts union select * from rts)
select pg_temp.parsavpg_translate_post(cn.orig,
coalesce(cn.promoter,0), coalesce(cn.promotion,0)
) as post,
cn.promotime::bigint as tltime,
coalesce(cn.promoter, (cn.orig).author) as promoter
from content as cn
order by cn.promotime desc
);
|
|
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
...
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
|
edited bigint,
parent bigint,
convoheaduri text,
chgcount integer,
-- ephemeral
accent smallint,
rtdby bigint, -- note that these must be 0 if the record
rtdat bigint, -- in question does not represent an RT!
rtid bigint, -- (this one too)
n_likes integer,
n_rts integer,
isreply bool -- true if parent in (table posts); saves us a bunch of queries
);
create or replace function
pg_temp.parsavpg_translate_post(parsav_posts,bigint,bigint,bigint)
returns pg_temp.parsavpg_intern_post as $$
select a.origin is null,
($1).id, ($1).author,
($1).subject,($1).acl, ($1).body,
($1).posted, ($1).discovered, ($1).edited,
($1).parent, ($1).convoheaduri,($1).chgcount,
coalesce(c.value, -1)::smallint,
$2 as rtdby, $3 as rtdat, $4 as rtid,
re.likes, re.rts,
($1).parent in (select id from parsav_posts)
from parsav_actors as a
left join parsav_actor_conf_ints as c
on c.key = 'ui-accent' and
c.uid = a.id
left join pg_temp.parsavpg_post_react_counts as re
................................................................................
inner join parsav_posts as p on a.subject = p.id
where a.kind = 'rt'
),
content as (select * from posts union select * from rts)
select pg_temp.parsavpg_translate_post(cn.orig,
coalesce(cn.promoter,0),
coalesce(cn.promotime,0),
coalesce(cn.promotion,0)
) as post,
cn.promotime::bigint as tltime,
coalesce(cn.promoter, (cn.orig).author) as promoter
from content as cn
order by cn.promotime desc
);
|