70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
body text,
posted bigint,
discovered bigint,
edited bigint,
parent bigint,
convoheaduri text,
chgcount integer,
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
);
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
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
on re.post = ($1).id
where a.id = ($1).author
|
|
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
|
body text,
posted bigint,
discovered bigint,
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
on re.post = ($1).id
where a.id = ($1).author
|