<commit_before>DROP FUNCTION IF EXISTS get_subscriptions(text);
CREATE OR REPLACE FUNCTION get_subscriptions(username text) RETURNS
  TABLE(name text) AS $$
DECLARE
  uid INT;
BEGIN
  SELECT INTO uid id FROM users WHERE users.name=username;
  RETURN QUERY SELECT users.name FROM user_subscriptions
    RIGHT JOIN users ON users.id = to_id
    WHERE fro_id=uid
    ORDER BY users.name
    ;
END
$$ LANGUAGE plpgsql;


DROP FUNCTION IF EXISTS delete_link(text,int);
CREATE OR REPLACE FUNCTION delete_link(username text, linkid int)
  RETURNS bool AS $$
DECLARE
  uid int;
  result bool;
BEGIN
  SELECT INTO uid id FROM users WHERE name=username;
  SELECT INTO result exists(SELECT * FROM user_links WHERE user_id=uid AND link_id=linkid);
  IF result THEN
    DELETE FROM user_links WHERE user_id=uid AND link_id=linkid;
  END IF;
  RETURN result;
END
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS get_bones(text, timestamp, int);
DROP FUNCTION IF EXISTS get_bones(text, int, timestamp);
CREATE OR REPLACE FUNCTION get_bones(username text, lim int, before timestamp)
  RETURNS TABLE(url text, title text, posted timestamp, poster text, total_votes bigint, subscriber_vote int) AS $$
DECLARE
  subscriber_id int;
BEGIN
  SELECT INTO subscriber_id id FROM users WHERE users.name = username;
  CREATE TEMP TABLE middle
    ON COMMIT DROP
    AS
      SELECT
        DISTINCT ON (links.url)
        links.url,links.title,links.posted,users1.name,total_votes(links.id),user_vote(subscriber_id,links.id)
        FROM user_subscriptions
        RIGHT JOIN user_links ON user_subscriptions.to_id=user_links.user_id
        INNER JOIN users ON users.id=fro_id
        LEFT JOIN users as users1 ON users1.id=to_id
        WHERE fro_id = subscriber_id
              AND
              links.posted < before ;
  RETURN QUERY SELECT * FROM middle ORDER BY middle.posted DESC LIMIT lim;
END
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS get_bones(text, int);
CREATE OR REPLACE FUNCTION get_bones(username text, lim int)
  RETURNS TABLE(url text, title text, posted timestamp, poster text, total_votes bigint, subscriber_vote int) AS $$
DECLARE
  subscriber_id int;
BEGIN
  SELECT INTO subscriber_id id FROM users WHERE users.name = username;
  CREATE TEMP TABLE middle
    ON COMMIT DROP
    AS
      SELECT
        DISTINCT ON (links.url)
        links.url,links.title,links.posted,users1.name,total_votes(links.id),user_vote(subscriber_id,links.id)<commit_msg>Fixing the "get before date" function<commit_after>DROP FUNCTION IF EXISTS get_subscriptions(text);
CREATE OR REPLACE FUNCTION get_subscriptions(username text) RETURNS
  TABLE(name text) AS $$
DECLARE
  uid INT;
BEGIN
  SELECT INTO uid id FROM users WHERE users.name=username;
  RETURN QUERY SELECT users.name FROM user_subscriptions
    RIGHT JOIN users ON users.id = to_id
    WHERE fro_id=uid
    ORDER BY users.name
    ;
END
$$ LANGUAGE plpgsql;


DROP FUNCTION IF EXISTS delete_link(text,int);
CREATE OR REPLACE FUNCTION delete_link(username text, linkid int)
  RETURNS bool AS $$
DECLARE
  uid int;
  result bool;
BEGIN
  SELECT INTO uid id FROM users WHERE name=username;
  SELECT INTO result exists(SELECT * FROM user_links WHERE user_id=uid AND link_id=linkid);
  IF result THEN
    DELETE FROM user_links WHERE user_id=uid AND link_id=linkid;
  END IF;
  RETURN result;
END
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS get_bones(text, timestamp, int);
DROP FUNCTION IF EXISTS get_bones(text, int, timestamp);
CREATE OR REPLACE FUNCTION get_bones(username text, lim int, before timestamp)
  RETURNS TABLE(url text, title text, posted timestamp, poster text, total_votes bigint, subscriber_vote int) AS $$
DECLARE
  subscriber_id int;
BEGIN
  SELECT INTO subscriber_id id FROM users WHERE users.name = username;
  CREATE TEMP TABLE middle
    ON COMMIT DROP
    AS
      SELECT
        DISTINCT ON (links.url)
        links.url,links.title,links.posted,users1.name,total_votes(links.id),user_vote(subscriber_id,links.id)
        FROM user_subscriptions
        RIGHT JOIN user_links ON user_subscriptions.to_id=user_links.user_id
        INNER JOIN links ON link_id=links.id
        INNER JOIN users ON users.id=fro_id
        LEFT JOIN users as users1 ON users1.id=to_id
        WHERE fro_id = subscriber_id
              AND
              links.posted < before ;
  RETURN QUERY SELECT * FROM middle ORDER BY middle.posted DESC LIMIT lim;
END
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS get_bones(text, int);
CREATE OR REPLACE FUNCTION get_bones(username text, lim int)
  RETURNS TABLE(url text, title text, posted timestamp, poster text, total_votes bigint, subscriber_vote int) AS $$
DECLARE
  subscriber_id int;
BEGIN
  SELECT INTO subscriber_id id FROM users WHERE users.name = username;
  CREATE TEMP TABLE middle
    ON COMMIT DROP
    AS
      SELECT
        DISTINCT ON (links.url)
        links.url,links.title,links.posted,users1.name,total_votes(links.id),user_vote(subscriber_id,links.id)
{'fraction_non_alphanumeric': 0.06294129513818843, 'fraction_numerical': 0.0016138793625176519, 'mean_word_length': 3.899209486166008, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5675968', 'n_tokens_mistral': 1664, 'n_tokens_neox': 1533, 'n_words': 531}