StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>For debugging, I created the test scenario:<br> <sub>You should have included that in your setup in the question.</sub></p> <pre><code>-- drop schema x CASCADE; create schema x create table x.users(id int); create table x.demographics (user_id int, gender text); INSERT INTO x.users VALUES (1),(2),(3),(4),(5); INSERT INTO x.demographics VALUES (1, 'm'),(2, 'f'),(3, 'm'),(4, 'f'),(5, 'm'); </code></pre> <p>This works now, after some fixes:</p> <pre><code>create type x.similarity as ( distance float, explanation text ); create or replace function x.similarity_gender(my_gender text, other_gender text) returns x.similarity as $$ declare distance float; sim x.similarity; begin if my_gender is null or other_gender is null then distance = 0.9; elsif (my_gender = other_gender) then distance = 0.0; else distance = 1.0; end if; sim.distance = distance; sim.explanation = hstore('gender', cast(sim.distance as text)); return sim; end; $$ language plpgsql immutable; create or replace function x.similarity(my_user_id int) returns table(user_id int, distance float, explanation text) as $$ with factors as ( select u.id as user_id, d.gender from x.users u join x.demographics d on u.id = d.user_id), my_factors as ( select f.user_id, f.gender from factors f where f.user_id = $1), similarities as ( select f.user_id, x.similarity_gender(m.gender, f.gender) AS sim from factors f, my_factors m) select s.user_id, (s.sim).distance, (s.sim).explanation from similarities s; $$ language sql stable strict; </code></pre> <p>Call:</p> <pre><code>test=# SELECT * FROM x.similarity(2); user_id | distance | explanation ---------+----------+--------------- 1 | 1 | "gender"=>"1" 2 | 0 | "gender"=>"0" 3 | 1 | "gender"=>"1" 4 | 0 | "gender"=>"0" 5 | 1 | "gender"=>"1" </code></pre> <h3>Major points</h3> <ul> <li>Create the function first, you have execution sequence reversed in your setup</li> <li>In function similarity you must qualify columns names to avoid name conflicts with OUT parameters of the same name (<code>user_id</code>, <code>distance</code>, <code>explanation</code>).</li> <li>Your CTE similarities was mangled. I pulled the function call similarity_gender(..) up into the SELECT list. In order not to call twice, I split it up in the next step.</li> <li>Use parenthesis to access the fields of composite types. Consult the <a href="http://www.postgresql.org/docs/current/interactive/rowtypes.html#AEN7249" rel="nofollow">fine manual here</a>.</li> <li>Return type of function similarity() had a bug: <code>explanation hstore</code>. Must be <code>explanation text</code>.</li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload