DPL talk:Manual - DPL with PostgreSQL

DPL broken with PostgreSQL and MW 1.12

Here's the example page on my wiki - any DPL I try to create on a page renders this error:

The DPL extension (version 1.6.5) produced a SQL statement which lead to a Database error.
The reason may be an internal error of DPL or an error which you made,
especially when using DPL options like titleregexp.
Query text is:
SELECT DISTINCT page.page_namespace as page_namespace,page.page_title as page_title, REPLACE(REPLACE(CONCAT( IF(page.page_namespace=0, , CONCAT(CASE page.page_namespace WHEN 1 THEN 'Talk' WHEN 2 THEN 'User' WHEN 3 THEN 'User_talk' WHEN 4 THEN 'DDOwiki' WHEN 5 THEN 'DDOwiki_talk' WHEN 6 THEN 'Image' WHEN 7 THEN 'Image_talk' WHEN 8 THEN 'MediaWiki' WHEN 9 THEN 'MediaWiki_talk' WHEN 10 THEN 'Template' WHEN 11 THEN 'Template_talk' WHEN 12 THEN 'Help' WHEN 13 THEN 'Help_talk' WHEN 14 THEN 'Category' WHEN 15 THEN 'Category_talk' WHEN 100 THEN 'Character' WHEN 101 THEN 'Character_talk' WHEN 102 THEN 'Guild' WHEN 103 THEN 'Guild_talk' END, ':')), page.page_title), '_', ' '),'♣','⣣') as sortkey FROM page INNER JOIN categorylinks AS cl0 ON page.page_id=cl0.cl_from AND (cl0.cl_to='Example_items') WHERE 1=1 AND page.page_is_redirect=0 ORDER BY sortkey ASC LIMIT 0, 500
Error message is:
ERROR: LIMIT #,# syntax is not supported HINT: Use separate LIMIT and OFFSET clauses.

I'd appreciate some help in getting this fixed, I don't know what's up! Elliottcable 23:45, 31 January 2008 (CET)

---

This seams to be fairly simple: change the SQL statement from "LIMIT 0, 500" to "OFFSET 0 LIMIT 500". Somewhere around line #2760 ...

If it works I will put it into the next release. Gero 10:44, 1 February 2008 (CET)

I can confirm the change to OFFSET # LIMIT # is needed and does help for postgres.--Danbrice 14:56, 15 June 2008 (CEST)

I can also confirm that the fix is needed. For those who aren't that code savvy... since it isn't in the current release, the change needed is to replace the following line (around line 2909):

$sSqlWhere .= " LIMIT $iOffset, " . intval( $iCount );

With the following line:

$sSqlWhere .= " LIMIT " . intval( $iCount ) . " OFFSET $iOffset";

-- Lcoffin 17:28, 9 October 2008 (UTC)

Media Wiki sets the search path

Seems that the open() function in includes/DatabasePostgres.php (at least in MW 1.11) sets the search path and overrides what ever you set it to - so instead of putting the if and concat functions in a new schema called mysql, I just added them to the public schema, which seems to work. Used the following sql:

create or replace function concat(text, text) returns text
as $function$
  begin
    return $1 || $2;
  end;
$function$ language plpgsql immutable strict;

create or replace function if(boolean, text, text) returns text
as $function$
  begin
    if $1 then
      return $2;
    else
      return $3;
    end if;
  end;
$function$ language plpgsql immutable strict;

--Danbrice 15:36, 15 June 2008 (CEST)

Regex with Posgtresq

Postgresql uses "~" instead of "REGEX" for doing regex searches. The following modifications worked for me on 1.7.4 for doing title regex (haven't tested the others):

<                     $sCategoryComparisonMode = ' REGEXP '; 
---
>                     $sCategoryComparisonMode = ' ~ ';

<                     $sNotCategoryComparisonMode = ' REGEXP ';
---
>                     $sNotCategoryComparisonMode = ' ~ ';

<                     $sTitleMatchMode = ' REGEXP ';
---
>                     $sTitleMatchMode = ' ~ ';

<                     $sNotTitleMatchMode = ' REGEXP ';
---
>                     $sNotTitleMatchMode = ' ~ ';

--Danbrice 17:25, 29 September 2008 (UTC)