SQL::Query::Limit::PostgreSQL - LIMIT emulation for PostgreSQL database.

SQL-Entity documentation Contained in the SQL-Entity distribution.

Index

Code Index:

NAME

Top

SQL::Query::Limit::PostgreSQL - LIMIT emulation for PostgreSQL database.

NAME

Top

SQL::Query::Limit::PostgreSQL

SYNOPSIS

Top

    use SQL::Query::Limit::PostgreSQL;

DESCRIPTION

Top

SQL navigation for PostgreSQL.

EXPORT

None.

ATTRIBUTES

the_rownum

METHODS

sql_definition
sql_defintion

Retuns sql defintion. Takes sql statement name.

query
the_rownum_column
sequence_name
query_setup

TODO. Improve collision in threads, - add ower to PLSQL

SEE ALSO

Top

SQL::Query.

COPYRIGHT AND LICENSE

Top

The SQL::Query::Limit::PostgreSQL module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

AUTHOR

Top

Adrian Witas, adrian@webapp.strefa.pl

SQL-Entity documentation Contained in the SQL-Entity distribution. 
package SQL::Query::Limit::PostgreSQL;

use warnings;
use strict;
use vars qw($VERSION);

$VERSION = '0.02';

use Abstract::Meta::Class ':all';
use SQL::Entity::Column ':all';
use SQL::Entity::Condition ':all';
use base 'SQL::Entity';


has '$.the_rownum';


{
    my %SQL = (
        find_sequence => "SELECT 1 AS has_seq FROM pg_class JOIN  pg_authid ON pg_class.relowner = pg_authid.oid
                WHERE pg_class.relkind = 'S' AND pg_class.relname = ? AND pg_authid.rolname = ?",
    );



    sub sql_defintion {
        my ($self, $name) = @_;
        $SQL{$name};
    }
}



sub query {
    my ($self, $offset, $limit, $requested_columns, $condition) = @_;
    my ($sql, $bind_variables) = $self->SUPER::query($requested_columns, $condition);
    $sql = "SELECT " . $self->alias . ".*," . $self->the_rownum_column->as_string
    . "\nFROM (\n" . $sql . ") " . $self->alias
    . "\nLIMIT ? OFFSET ?";
    push @$bind_variables, $limit, ($offset - 1);
    ($sql, $bind_variables);
}

   

sub the_rownum_column {
    my ($self) = @_;
    my $the_rownum = $self->the_rownum;
    $the_rownum ||= $self->the_rownum(SQL::Entity::Column->new(name => "nextval('". $self->sequence_name  . "')", id => 'the_rownum'));
}



sub sequence_name {
    'rownum';
}



sub query_setup {
    my ($self, $connection) = @_;
    my $sequence_name = $self->sequence_name;
    my $result_set = $connection->record($self->sql_defintion('find_sequence'), $sequence_name, $connection->username);
    if ($result_set->{has_seq}) {
        $connection->do("SELECT setval('${sequence_name}', 1);") if $result_set->{has_seq};
    } else {
        $connection->do("create temp sequence " . $sequence_name) unless $result_set->{has_seq};
    }
}

1;

__END__