StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODuplicate Records From Postgres SELECT SETOF Function
text
Body
copied!<p>I am returning a SETOF from a Postgres FUNCTION to PHP/PDO. Unfortunately, I am getting 2 copies of every row returned.</p> <p>Here is the Postgres TYPE:</p> <pre><code>CREATE TYPE marker AS (i BOOLEAN, r DOUBLE PRECISION, la DOUBLE PRECISION, lo DOUBLE PRECISION, n INTEGER); </code></pre> <p>And the FUNCTION:</p> <pre><code>CREATE OR REPLACE FUNCTION rl_select_markers (latitude DOUBLE PRECISION, longitude DOUBLE PRECISION) RETURNS setof marker AS $$ DECLARE ROW marker%ROWTYPE; BEGIN FOR ROW IN SELECT is_male, rate, lat, lon, (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - created_at)/60)::INTEGER FROM markers WHERE expires_at > CURRENT_TIMESTAMP ORDER BY ST_Distance(ST_Point(longitude, latitude), geog, FALSE) LIMIT 25 LOOP RETURN NEXT ROW; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; </code></pre> <p>Here is the relevant PHP:</p> <pre><code>$dbh = new PDO('pgsql:host=' . $host . ';dbname=' . $db, $user, $pw); $stmt = $dbh->prepare("SELECT rl_select_markers (:lat, :lon)"); $stmt->bindParam(':lat', $lat, PDO::PARAM_INT); $stmt->bindParam(':lon', $lon, PDO::PARAM_INT); $stmt->execute(); $keys = array('i', 'r', 'la', 'lo', 'n'); $markers = array(); while ($lines = $stmt->fetch()) { $log->logInfo($log_id . " lines=" . $lines); $combined = array(); foreach ($lines as $line) { $log->logInfo($log_id . " line=" . $line); // Remove brackets around $line and put in array $vals = explode(",", substr($line, 1, strlen($line)-2)); // Combine into key value pairs $combined = array_combine($keys, $vals); } $markers[] = array("m"=>$combined); } $dbh = null; $stmt = null; $log->logInfo($log_id . " Send 200 OK"); sendResponse(200, json_encode(array("mks"=>$markers)), "application/json"); </code></pre> <p>The unexpected behaviour is that each <code>$lines</code> is an array of two copies of the same line. </p> <p>E.G. When there is one record to return <code>$lines</code> would be an array of 2 strings that are exactly the same. So the above logging will produce:</p> <pre><code>lines=Array line=(f,10,51.505601,-0.109917,8) line=(f,10,51.505601,-0.109917,8) </code></pre> <p>I understand my inner PHP loop is nonsense but it works. I can see work arounds (such as add a <code>break;</code>)but want to remove the duplication.</p> <p>I hope I have explained this clearly. Can you see why I am getting these duplicates of every record?</p> <p><strong>Edit</strong></p> <p>Following @a_horse_with_no_name's comment question:</p> <p>Calling the SQL or the FUNCTION from phpPgAdmin does <strong>not</strong> produce the duplicate.</p>

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