Re: [SQL] How to Return Table From Function
On 01/22/2012 06:09 AM, Rehan Saleem wrote: hi , i have created this function CREATE OR REPLACE FUNCTION totaloverlapcount(user_id integer , bg integer ,center_distance integer) RETURNS varchar AS $$ DECLARE percentage record; BEGIN select fname, lname, count(userid) totalcount ,100.00*count(useriddetails)/totaluser into percentage from users where userid= user_id and bloodgroup>=bg and (centredistance<=center_distance or center_distance=1) group by fname, lname, user_id; return percentage; its just a dummy function , but all i want to know that how can i return fname , lname totalcount and percentage from this function in the form of table , not the return type varchar. thanks -Sorry cannot get to a server at the moment to check this sql, but if you want to address the field of the result table you need to create the type for the returned table. create type reportTypeName(fname text, lname text, totalcount integer); CREATE OR REPLACE FUNCTION totaloverlapcount(user_id integer , bg integer ,center_distance integer) RETURNS setof reportTypeName AS $$ Then you can do select lname, totalcount as tally from totaloverlapcount( id, bg, cdist);
Re: [SQL] How to compare two tables in PostgreSQL
On 11/10/2012 08:13 PM, saikiran mothe wrote: Hi, How can i compare two tables in PostgreSQL. Thanks, Sai Compare their content or their definition? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] \copy multiline
On 11/29/2012 02:33 AM, Guillaume Lelarge wrote: On Wed, 2012-11-28 at 21:21 -0600, Seb wrote: Hi, I use \copy to output tables into CSV files: \copy (SELECT ...) TO 'a.csv' CSV but for long and complex SELECT statements, it is cumbersome and confusing to write everything in a single line, and multiline statements don't seem to be accepted. Is there an alternative, or am I missing an continuation-character/option/variable that would allow multiline statements in this case? A simple way to workaround this issue is to create a view with your query and use the view in the \copy meta-command of psql. Of course, it means you need to have the permission to create views in the database. Or maybe a function returning a table or set of records. Might be slightly more flexible than the view. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Presenting data in 5 Rows & 5 Cols for 25 specific values
On 08/03/2013 07:26 AM, F Bax wrote:
I have a table containing tasks completed in a game I'm playing. The
game includes an extra BINGO Challenge where each cell of standard
BINGO card contains a particular task to be completed. The goal is
score a BINGO (row, column, diagonal) by completing five (or more)
tasks from the BINGO cards. My task table contains more tasks
completed than the one included in the BINGO challenge.
SELECT task, CASE WHEN task='Task27' THEN 'R1C1' WHEN task='Task32'
THEN 'R1C2' ... WHEN task='Task94' THEN 'R5C5' END AS bingo FROM tasks
WHERE bingo IS NOT NULL;
This query will retrieve all tasks related to the BINGO that I have
completed and present them in a simple list. I would like to arrange
the tasks as a BINGO card; so that I can easily see my progress on
various rows & columns working toward a BINGO.
Any suggestions?
BONUS points will be awarded if the query displays a row with 5 NULL
values if no tasks are completed in that row.
I don't think you've fully described your tables so I've ad libbed a
little. This is not a complete solution (and certainly no bonus point)
but it should give you one starting point. Your CTE will have to deal
with transforming the RmCn into something more useful
create table bingo (ttype text, username text, task text, r int, c int);
begin;
insert into bingo values
('bingo','me','task0',0, 0),
('bingo','me','task1',0, 1),
('bingo','me','task2',0, 2),
('bingo','me','task3',0, 3),
('bingo','me','task4',0, 4),
('bingo','me','task5',1, 0),
('bingo','me','task6',1, 1),
('bingo','me','task7',1, 2),
('bingo','me','task8',1, 3),
('bingo','me','task9',1, 4),
('bingo','me','task10',2, 0),
...
('bingo','me','task71',14, 1),
('bingo','me','task72',14, 2),
('bingo','me','task73',14, 3),
('bingo','me','task74',14, 4)
with bingoline as (
select r, array_agg(task) as taskline from bingo where mod(r,5) = 0 and
username = 'me' group by r
union--
select r, array_agg(task) as taskline from bingo where mod(r,5) = 1 and
username = 'me' group by r
union--
select r, array_agg(task) as taskline from bingo where mod(r,5) = 2 and
username = 'me' group by r
union--
select r, array_agg(task) as taskline from bingo where mod(r,5) = 3 and
username = 'me' group by r
union--
select r, array_agg(task) as taskline from bingo where mod(r,5) = 4 and
username = 'me' group by r
)
select array_to_string(bl.taskline, '|', 'nil') from bingoline bl order
by bl.r;
;
Bingo Card
task0|task1|task2|task3|task4
task5|task6|task7|task8|task9
task10|task11|task12|task13|task14
task15|task16|task17|task18|task19
task20|task21|task22|task23|task24
task25|task26|task27|task28|task29
task30|task31|task32|task33|task34
task35|task36|task37|task38|task39
task40|task41|task42|task43|task44
task45|task46|task47|task48|task49
task50|task51|task52|task53|task54
task55|task56|task57|task58|task59
task60|task61|task62|task63|task64
task65|task66|task67|task68|task69
task70|task71|task72|task73|task74
(15 rows)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Presenting data in 5 Rows & 5 Cols for 25 specific values
On 08/03/2013 07:26 AM, F Bax wrote:
I have a table containing tasks completed in a game I'm playing. The
game includes an extra BINGO Challenge where each cell of standard
BINGO card contains a particular task to be completed. The goal is
score a BINGO (row, column, diagonal) by completing five (or more)
tasks from the BINGO cards. My task table contains more tasks
completed than the one included in the BINGO challenge.
SELECT task, CASE WHEN task='Task27' THEN 'R1C1' WHEN task='Task32'
THEN 'R1C2' ... WHEN task='Task94' THEN 'R5C5' END AS bingo FROM tasks
WHERE bingo IS NOT NULL;
This query will retrieve all tasks related to the BINGO that I have
completed and present them in a simple list. I would like to arrange
the tasks as a BINGO card; so that I can easily see my progress on
various rows & columns working toward a BINGO.
Any suggestions?
BONUS points will be awarded if the query displays a row with 5 NULL
values if no tasks are completed in that row.
I haven't noticed any posting showing your exact table definition, but
here's more still running on my assumptions and short-cuts. Of course
this would all be trivial if the data were sucked into any reasonable
language adn delt with there for shipment to the UI.
Adding this list of accomplished tasks (the first diagonal)
insert into bingo values
('bingo','rjs','task0',0, 0),
('bingo','rjs','task6',1, 1),
('bingo','rjs','task12',2, 2),
('bingo','rjs','task18',3, 3),
('bingo','rjs','task24',4, 4)
;
\pset null 'not done'-- you'll probably need to use outer joins and to
coalesce null values.
select r,c,
(select task from bingo i where c = 0 and username = 'rjs'
and i.task = b.task and mod(r,5) = 0) as "B",
(select task from bingo i where c = 1 and username = 'rjs'
and i.task = b.task and mod(r,5) = 1) as "I",
(select task from bingo i where c = 2 and username = 'rjs'
and i.task = b.task and mod(r,5) = 2) as "N",
(select task from bingo i where c = 3 and username = 'rjs'
and i.task = b.task and mod(r,5) = 3) as "G",
(select task from bingo i where c = 4 and username = 'rjs'
and i.task = b.task and mod(r,5) = 4) as "O"
from bingo b
where username = 'rjs'
;
r | c |B |I |N |G | O
---+---+--+--+--+--+--
0 | 0 | task0| not done | not done | not done | not done
1 | 1 | not done | task6| not done | not done | not done
2 | 2 | not done | not done | task12 | not done | not done
3 | 3 | not done | not done | not done | task18 | not done
4 | 4 | not done | not done | not done | not done | task24
(5 rows)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
