{"action":"created","issue":{"url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/181","repository_url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye","labels_url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/181/labels{/name}","comments_url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/181/comments","events_url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/181/events","html_url":"https://github.com/tomjaguarpaw/haskell-opaleye/issues/181","id":159975928,"number":181,"title":"Call a stored procedure","user":{"login":"lippling","id":85933,"avatar_url":"https://avatars.githubusercontent.com/u/85933?v=3","gravatar_id":"","url":"https://api.github.com/users/lippling","html_url":"https://github.com/lippling","followers_url":"https://api.github.com/users/lippling/followers","following_url":"https://api.github.com/users/lippling/following{/other_user}","gists_url":"https://api.github.com/users/lippling/gists{/gist_id}","starred_url":"https://api.github.com/users/lippling/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lippling/subscriptions","organizations_url":"https://api.github.com/users/lippling/orgs","repos_url":"https://api.github.com/users/lippling/repos","events_url":"https://api.github.com/users/lippling/events{/privacy}","received_events_url":"https://api.github.com/users/lippling/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":12,"created_at":"2016-06-13T15:19:13Z","updated_at":"2016-08-25T15:27:13Z","closed_at":null,"body":"I want to run the following query:\r\n\r\n```sql\r\nSELECT hws_get_deleted_ids('questions', '2016-06-13 12:00:00'::TIMESTAMPTZ, 'content_id', ARRAY []::TEXT[], 1)\r\n```\r\n\r\nI drafted this function:\r\n\r\n```haskell\r\ngetDeletedIds :: Table writerColumns readerColumns -> Column PGTimestamptz -> Language.IdColumn -> Column PGInt4\r\ngetDeletedIds table (Column sinceWhen) (Language.Id (Column languageId)) = Column\r\n    ( HPQ.FunExpr \"hws_get_deleted_ids\"\r\n        [ HPQ.ConstExpr . HPQ.StringLit . HPQ.tiTableName . T.tableIdentifier $ table\r\n        , sinceWhen\r\n        , HPQ.ConstExpr . HPQ.StringLit $ \"content_id\"\r\n        , HPQ.ArrayExpr []\r\n        , languageId\r\n        ]\r\n    )\r\n```\r\n\r\n1. How can I run it to get the `[Int]`?\r\n2. How can I tell Postgres the correct type for the empty array or does this happen automatically in Opaleye?\r\n3. What's the best way to get a column name (`content_id` is a string now, but should be provided to `getDeletedIds` in a type-safe way later)?"},"comment":{"url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/comments/242428975","html_url":"https://github.com/tomjaguarpaw/haskell-opaleye/issues/181#issuecomment-242428975","issue_url":"https://api.github.com/repos/tomjaguarpaw/haskell-opaleye/issues/181","id":242428975,"user":{"login":"lippling","id":85933,"avatar_url":"https://avatars.githubusercontent.com/u/85933?v=3","gravatar_id":"","url":"https://api.github.com/users/lippling","html_url":"https://github.com/lippling","followers_url":"https://api.github.com/users/lippling/followers","following_url":"https://api.github.com/users/lippling/following{/other_user}","gists_url":"https://api.github.com/users/lippling/gists{/gist_id}","starred_url":"https://api.github.com/users/lippling/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lippling/subscriptions","organizations_url":"https://api.github.com/users/lippling/orgs","repos_url":"https://api.github.com/users/lippling/repos","events_url":"https://api.github.com/users/lippling/events{/privacy}","received_events_url":"https://api.github.com/users/lippling/received_events","type":"User","site_admin":false},"created_at":"2016-08-25T15:27:13Z","updated_at":"2016-08-25T15:27:13Z","body":"It's only a `Content.Id`:\r\n\r\n```sql\r\nCREATE FUNCTION hws_get_user_content_ids(v_user_id users.id%TYPE, v_language_id languages.id%TYPE) RETURNS SETOF contents.id%TYPE AS $$\r\n    BEGIN\r\n        RETURN QUERY\r\n            WITH RECURSIVE contents_of_user AS (\r\n                    SELECT ch.child_content_id AS content_id\r\n                    FROM content_hierarchies ch\r\n                    JOIN contents c ON c.id = ch.child_content_id\r\n                    JOIN text_translations tt ON tt.text_id = c.title_text_id AND tt.language_id = $2\r\n                    WHERE child_content_id IN (SELECT content_id FROM users_contents WHERE user_id = $1)\r\n                UNION\r\n                    SELECT ch.child_content_id AS content_id\r\n                    FROM content_hierarchies ch\r\n                    JOIN contents c ON c.id = ch.child_content_id\r\n                    JOIN text_translations tt ON tt.text_id = c.title_text_id AND tt.language_id = $2\r\n                    JOIN contents_of_user cou ON ch.parent_content_id = cou.content_id\r\n            )\r\n            SELECT * FROM contents_of_user;\r\n    END;\r\n$$ LANGUAGE plpgsql;\r\n```"}}
{"actor":{"display_login":"lippling"}}