Skip site navigation (1) Skip section navigation (2)

Known problem? Column mixup in sql-function

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Known problem? Column mixup in sql-function
Date: 2011-08-29 15:26:16
Message-ID: 4E5BAF98.5040404@gmx.net (view raw or flat)
Thread:
Lists: pgsql-novice
Hi,
I found a weired issue with a rather simple sql-function.
There are tables for projects, users and a m:n-relation between users 
and projects where an user-id and a project-id say that this user has 
acces to this project.
The function should filter all projects a user has access to.

This works nicely with a PG 8.4.8 on Windows but fails with a PG 8.4.0 
on openSuse 11.1.
Well I know, OpenSuse 11.1 is out of support allread but I can't help it 
for now.

On Suse the columns are mixed up.
The column names are in the same order as on windows but the data apears 
in the wrong columns and some of the columns to the right show no data 
at all.
I can run the query outside the function and it looks ok.
It seems that the "returns setof projects" doesn't work.
Is this a known problem?

CREATE OR REPLACE FUNCTION projects_with_access(integer)
   RETURNS SETOF projects AS
$BODY$
     SELECT  projects.*
     FROM    projects
             JOIN projectaccessrights USING ( project_id )
     WHERE   user_id = $1;
$BODY$

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2011-08-29 16:10:21
Subject: Re: Known problem? Column mixup in sql-function
Previous:From: Merlin MoncureDate: 2011-08-29 13:41:09
Subject: Re: How to correct schema-names within function when the schema gets renamed?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group