PostgreSQL return table with update and select statement causing ambiguity

Question

I'm working on a small art gallery web site for a friend of mine and have decided to go with PostgreSQL for various reasons. So far everything is working good but I've hit a small snag. The problem is with the below function.

I'm having ambiguity issues with the viewcount column. The conflict is between the update statement and the returning table. I'm not quite sure how to resolve this other than altering the returning table column viewcount to something like views or creating another function to update the count.

My basic knowledge of SQL comes from my work where we use MSSQL.

create or replace function submission_getone
(
    int         -- submissionid
    ,boolean    -- upcount
    ,int        -- mmask
    ,int        -- statemask
)
returns table
(
    submissionid int
    ,galleryid int
    ,gallerytitle varchar(100)
    ,createdby int
    ,createdbyname varchar(32)
    ,alteredby int
    ,alteredbyname varchar(32)
    ,createdon timestamp
    ,alteredon timestamp
    ,title varchar(100)
    ,content text
    ,file1 varchar(64)
    ,viewcount int
    ,mlevel int
    ,typecode int
    ,statecode int
)
as
$$
declare
    _submissionid   alias for $1;
    _upcount        alias for $2;
    _mmask          alias for $3;
    _statemask      alias for $4;
begin
    -- because the member may not want to see specific content (mmask)
    -- and because the submitter my have not published the content (statemask),
    -- include mmask and statemask in the where clause

    -- referenced this for aliases in an update
    -- http://stackoverflow.com/questions/11369757/postgres-wont-accept-table-alias-before-column-name
    if _upcount = true then
        update submission us set 
            viewcount = viewcount + 1 
        where us.submissionid = _submissionid 
            and (us.mlevel & _mmask) = us.mlevel
            and (us.statecode & _statemask) = us.statecode;
    end if;

    return query
    select
        s1.submissionid
        ,s1.galleryid
        ,coalesce(g1.title, 'Orphan')::varchar(100) as gallerytitle
        ,s1.createdby
        ,m1.accountname
        ,s1.alteredby
        ,m2.accountname
        ,s1.createdon
        ,s1.alteredon
        ,s1.title
        ,s1.content
        ,s1.file1
        ,s1.viewcount
        ,s1.mlevel
        ,s1.typecode
        ,s1.statecode
    from submission s1
    left join gallery g1 on s1.galleryid = g1.galleryid
    join member m1 on s1.createdby = m1.memberid
    join member m2 on s1.alteredby = m2.memberid
    where s1.submissionid = _submissionid
            and (s1.mlevel & _mmask) = s1.mlevel
            and (s1.statecode & _statemask) = s1.statecode;
end;
$$
language plpgsql;

You forgot your version of Postgres and the error message you got. — Erwin Brandstetter, Apr 28 '13 at 01:51

Erwin Brandstetter · Accepted Answer · 2013-04-28T02:11:27.967

2

You cannot table-qualify the column to be updated, but you can (and must, in this case) table-qualify columns in the expression used:

SET  viewcount = us.viewcount + 1

BTW, in Postgres 8.0 or later, you can (and should) use parameter names instead of aliases:

CREATE OR REPLACE FUNCTION submission_getone (
  _submissionid int
 ,_upcount boolean
 ,_mmask int
 ,_statemask int
)

... and get get rid of this:

declare
    _submissionid   alias for $1;
    _upcount        alias for $2;
    _mmask          alias for $3;
    _statemask      alias for $4;

RETURNS TABLE indicates you got at least Postgres 8.4.

edited Apr 28 '13 at 02:11

answered Apr 28 '13 at 02:00

Erwin Brandstetter

605,456
145
1,078
1,228

Shows how new I am to PSQL. Altered the update statement and dropped the declare block. Works great now. Thank you Erwin. – WellNow Apr 28 '13 at 02:17
@WellNow: The function is otherwise nicely done. And the part that made you trip *is* tricky. – Erwin Brandstetter Apr 28 '13 at 02:21

PostgreSQL return table with update and select statement causing ambiguity

1 Answers1