PostgreSQL 9.3 – Using Subquery in GRANT Statement

postgresql-9.3subquery

I want to grant select privilege on few selected tables.

Can I use sub-query in GRANT statement????

I have tried the following query:

GRANT SELECT ON TABLE  
(SELECT array_to_string( array_agg(table_schema||'.'||table_name),', ' ) as  flttab 
FROM information_schema.tables 
where table_schema = 'try3' AND table_name like 'ph1_part_03%') a TO xyz;

But I have got the below error:

ERROR: syntax error at or near "("
LINE 5: GRANT SELECT ON TABLE (SELECT array_to_string( array_agg(ta…
^

********** Error **********

ERROR: syntax error at or near "("
SQL state: 42601
Character: 282

Best Answer

No that's not possible.

The typical solution for this kind of problems is to either use dynamic SQL, or to use a query that generates the desired statement. Then spool that to a script and run the script.

To use dynamic SQL you can use a DO block

do
$body$
declare 
  table_list text;
begin 
  SELECT array_to_string( array_agg(table_schema||'.'||table_name),', ' ) 
    into table_list
  FROM information_schema.tables 
  where table_schema = 'try3' 
  AND table_name like 'ph1_part_03%';

  if table_list is not null then 
    execute 'GRANT SELECT ON '||table_list||' TO xyz';
  end if;
end;
$body$

Related Solutions

Use an Alias in Where Clause Subquery

@haki has identified why this error occurs. You can't use an alias (KODE) defined in the SELECT list, in the definition of another column in that list. You have to use the full reference instead (ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI).

But the solution provided, does not work I think because of the double nesting. You can reference a column in a inline subquery but not in a derived table inside an inline subquery.

This would require either using ROW_NUMBER() and encapsulating the whole query ina derived table or turning the inline subquery into a LEFT JOIN. But since you have ROWNUM=1 and ORDER BY TMT_JABATAN desc and you are selecting only TMT_JABATAN in that inline subquery, you can use MAX(TMT_JABATAN) instead and one-level nesting:

SELECT
    sp.ID_PEGAWAI AS KODE,
    sp.NAMA,
    sp.NIP,
    sej.JABATAN,
    skgp.GOLONGAN,
    skgp.PANGKAT,
    ( SELECT CONCAT(TO_CHAR(abs(sysdate - TO_DATE(MAX(sj.TMT_JABATAN)))/360,'9,999,999.9'),
                    ' TAHUN') 
      FROM SIMPEG_JABATAN  sj
      WHERE sj.ID_PEGAWAI = sp.ID_PEGAWAI  
    ) AS MASA_KERJA
FROM 
        ANGGARAN.SIMPEG_PEGAWAI  sp
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN   sej
        ON sp.ESELON_JABATAN = sej.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT  skgp
        ON sp.PANGKAT = skgp.ID_GOLONGAN_PANGKAT
WHERE
    sp.ST_AKTIF = 1 AND
    sp.ESELON2 <> 1 AND
    sp.PANGKAT >= 12 AND 
    sej.STATUS = 1 AND
    sej.ID_ESELON2 = 2
ORDER BY
    skgp.SORT DESC ;

Mysql – Slow subquery in SELECT statement

Try rewriting the sub-query as a join:

SELECT YEAR(b1.checkin) as year
     , MONTH(b1.checkin) as monthnum,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON YEAR(b2.checkin) = YEAR(b1.checkin) 
   AND MONTH(b2.checkin) <= MONTH(b1.monthnum)
GROUP BY YEAR(b1.checkin),MONTH(b1.checkin)
ORDER BY YEAR(b2.checkin) ASC, MONTH(b2.checkin) ASC

I also removed CONCAT(UCASE(LEFT(MONTHNAME(checkin), 1)),SUBSTRING(MONTHNAME(checkin), 2)) as month, I don't think that will matter but it might be beneficial to do this once, and add it to the report in the application-layer.

You don't mention version of MySQL, but later versions have generated columns. You can try generating columns for YEAR and MONTH:

CREATE TABLE IF NOT EXISTS bookings 
( DBID int unsigned NOT NULL AUTO_INCREMENT
, checkin date NOT NULL
, checkout date NOT NULL
, total int NOT NULL

, checkin_year SMALLINT as (YEAR(checkin))
, checkin_month SMALLINT as (MONTH(checkin)) 

,    PRIMARY KEY (DBID, checkin)
,    KEY total (total)

,    KEY (checkin_year, checkin_month)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11171 ;

and then use those columns in the query:

SELECT b1.checkin_year
     , b1.checkin_month,
     , SUM(b2.total)
FROM bookings as b1
JOIN bookings as b2
    ON b2.checkin_year = b1.checkin_year 
   AND b2.checkin_month <= b1.checkin_month
GROUP BY b1.checkin_year, b1.checkin_month
ORDER BY b1.checkin_year, b1.checkin_month

BTW, what is the purpose of the index on TOTAL?

Best Answer

Related Solutions

Use an Alias in Where Clause Subquery

Mysql – Slow subquery in SELECT statement

Related Question