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
– Erwin Brandstetter, Commented Apr 28, 2013 at 1:51

Erwin Brandstetter · Accepted Answer · 2013-04-28 02:11:27Z

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, 2013 at 2:11

answered Apr 28, 2013 at 2:00

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WellNow Over a year ago

Shows how new I am to PSQL. Altered the update statement and dropped the declare block. Works great now. Thank you Erwin.

Erwin Brandstetter Over a year ago

@WellNow: The function is otherwise nicely done. And the part that made you trip is tricky.

Collectives™ on Stack Overflow

PostgreSQL return table with update and select statement causing ambiguity

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related