HUSNG min stack for reports

[BillGatesIII]

That is a strange error. Did you just copy-paste the code? And are you running Windows, Linux or Mac?

This is how it should look like in Windows.

And fwiw, I never install language packs, everything is English on my system.

[pt4pt4pt4]

Running Windows and I can vary the error if I change the properties language and return type.

The error I get now with this setup is
"ERROR: return type mismatch in function declared to return bigint
DETAIL: Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING
CONTEXT: SQL function "current_position"


I never have messed with PGAdmin iII so searching I just tried basic options-

Properties:
Name :current_position
Owner : Postgres
Return Type: Bigint
language: SQL

Options:
Volatility: Volatile

Parameters:

Definition:

Spoiler: show

Code: Select all

CREATE OR REPLACE FUNCTION current_position(tourney_hand_player_statistics)
  RETURNS bigint AS
$BODY$
select all_players.cur_pos
from (select thps.id_player, row_number() over (order by thps.amt_before - thps.amt_blind - thps.amt_ante + thps.amt_won desc) as cur_pos
      from tourney_hand_player_statistics thps
      where thps.id_hand = (select max(ths.id_hand)
                            from tourney_hand_summary ths
                            where ths.id_tourney = $1.id_tourney)) as all_players
where all_players.id_player = $1.id_player;
$BODY$
  LANGUAGE sql STABLE
  COST 100;
ALTER FUNCTION current_position(tourney_hand_player_statistics)
  OWNER TO postgres;

variables:

Variable.........................Value
current_position...............DEFAULT


Vatriable_name: current_position default:
variable Value: DEFAULT


Privileges:

SQL;

Code: Select all

CREATE FUNCTION current_position() RETURNS bigint AS
$BODY1$CREATE OR REPLACE FUNCTION current_position(tourney_hand_player_statistics)
  RETURNS bigint AS
$BODY$
select all_players.cur_pos
from (select thps.id_player, row_number() over (order by thps.amt_before - thps.amt_blind - thps.amt_ante + thps.amt_won desc) as cur_pos
      from tourney_hand_player_statistics thps
      where thps.id_hand = (select max(ths.id_hand)
                            from tourney_hand_summary ths
                            where ths.id_tourney = $1.id_tourney)) as all_players
where all_players.id_player = $1.id_player;
$BODY$
  LANGUAGE sql STABLE
  COST 100;
ALTER FUNCTION current_position(tourney_hand_player_statistics)
  OWNER TO postgres;$BODY1$
LANGUAGE sql VOLATILE;
ALTER FUNCTION current_position() OWNER TO postgres;
ALTER FUNCTION current_position() SET current_position='DEFAULT';

[BillGatesIII]

The last SQL in your post is incorrect. I don't know what happened but for example there is no space between $BODY1$ and CREATE OR ... That will generate an error. I also do not understand why it states $BODY1$ instead of $BODY$. You'll have to copy and paste the exact code that's in my original post (which is the same as in your post in the spoiler section). There are no changes needed if you're using PostgreSQL 9.

[pt4pt4pt4]

If I edit what you suggested in the SQL section (remove read only checkmark) and select OK, it tells me now that I have an error

ERROR: syntax at or mnear "select"
LINE 5: select all_players.cur_pos

It doesn't matter copy and paste since it changes back to the incorrect format as you pointed in previous post (body, space problems).


I did notice one thing:


"row_number() over (order by thps.amt_before"

The "over" is not in blue like your example is.

Code: Select all

CREATE FUNCTION current_position() RETURNS bigint AS
$BODY$ CREATE OR REPLACE FUNCTION current_position(tourney_hand_player_statistics)
  RETURNS bigint AS
$BODY$
select all_players.cur_pos
from (select thps.id_player, row_number() over (order by thps.amt_before - thps.amt_blind - thps.amt_ante + thps.amt_won desc) as cur_pos
      from tourney_hand_player_statistics thps
      where thps.id_hand = (select max(ths.id_hand)
                            from tourney_hand_summary ths
                            where ths.id_tourney = $1.id_tourney)) as all_players
where all_players.id_player = $1.id_player;
$BODY$
  LANGUAGE sql STABLE
  COST 100;
ALTER FUNCTION current_position(tourney_hand_player_statistics)
  OWNER TO postgres;$BODY1$
LANGUAGE sql VOLATILE;
ALTER FUNCTION current_position() SET current_position='DEFAULT';

[BillGatesIII]

To make things clear for both of us: Did you use the SQL that was in your last post? Because that's still wrong (for example, two times $BODY$ in the beginning, $BODY1$ at the end) and will not work. Or did you use the original SQL?

[pt4pt4pt4]

To make things clear for both of us: Did you use the SQL that was in your last post? Because that's still wrong (for example, two times $BODY$ in the beginning, $BODY1$ at the end) and will not work. Or did you use the original SQL?

I thought u post the code in the definition and it automatically converts it to the SQL definition properly.

This time I posted directly into the SQL option (removed read only option) and it saved it, np.

I'm on to the testing part now

[pt4pt4pt4]

It works with SNG & MTT SNGs, not so much with MTTs and MTTs Sats.

Seems to be hanging up looking for MTT chip counts that are continuously changing?

*Edit seemed to work better after shut down the SNGs, but takes a long time to update the positions.

To change

replace ROW_NUMBER() with RANK() in the function

after I created it, option or recreate another?


Is there a easy way to specify the range of chips to influence Rank?

Works very good with SNGs.

Nice feature to have

[BillGatesIII]

It only knows the chipcounts of the players at your table so you should use it only with single table tournaments. Although I guess it will work for 18 or 27 man once you're at the last table.

You can replace row_number() with rank() if you want players with equal stacks have the same ranking. It will replace the original function so it's an option which one you want to use.

[pt4pt4pt4]

It only knows the chipcounts of the players at your table so you should use it only with single table tournaments. Although I guess it will work for 18 or 27 man once you're at the last table.

You can replace row_number() with rank() if you want players with equal stacks have the same ranking. It will replace the original function so it's an option which one you want to use.

It worked fine at a 18 man SNG, updated the positions at the table and then the final table.

[BillGatesIII]

That's good to hear. The more I think about it and look at what I coded, the more I think it will always work, regardless of the size of the sng/tourney. It might go wrong if there are observed hands, I'm note sure.